• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Programming Expert

Solving All of Your Programming Headaches

  • HTML
  • JavaScript
  • jQuery
  • PHP
  • Python
  • SAS
  • Ruby
  • About
You are here: Home / SAS / SAS calculated – Use Columns Created from Select in PROC SQL

SAS calculated – Use Columns Created from Select in PROC SQL

April 12, 2022 Leave a Comment

When using PROC SQL in SAS, calculated allows you to use columns created from calculations in a select clause in the having clause or another variable calculation.

proc sql;
     create table new_table as 
     select id, name, sum(sales) as total_sales 
     from old_table
     group by name
     where calculated total_sales > 100
     order by name;
quit;

PROC SQL is one of the most commonly used procedures in SAS. When using PROC SQL, we are able to create complex queries and create new datasets.

In PROC SQL, sometimes we went to be able to create new variables and then in the same step, filter our data based on this calculated variable.

To filter data in PROC SQL with a newly calculated variable, we need to use calculated.

calculated allows you to use columns created from calculations in a select clause in the having clause or to create other variables.

Let’s say we have the following sales data in SAS.

data sales;
	input name $5. sales;
	datalines;
Jim   84
Jim   19
Bob   5
Bob   31
Bonny 69
Bonny 82
;
run;

If we wanted to group by name and get a sum of sales for each person, but only keep the people with more than 100 in sales, then we can use calculated as shown in the following SAS code.

proc sql;
     create table sales_sum as 
     select name, sum(sales) as total_sales 
     from sales
     group by name
     where calculated total_sales > 100
     order by name;
quit;

The output from this PROC SQL block is shown below:

      name    total_sales
1    Bonny            151	
2      Jim            103

Using Calculated Variables in Select Clause to Create New Variables with PROC SQL

You can also use calculated to create new variables from previously created variables in a select clause in a PROC SQL block.

Just as shown above, just add calculated before using the newly calculated and created variable.

Let’s say we have the same data from above.

We can use calculated to create new variables in a select clause from other newly created variables as shown below.

proc sql;
     create table sales_sum as 
     select name, sum(sales) as total_sales, calculated total_sales * 2 as doubled_sales
     from sales
     group by name
     having calculated total_sales > 100
     order by name;
quit;

The output from this PROC SQL block is shown below:


      name    total_sales  doubled_sales
1    Bonny            151            302	
2      Jim            103            206

Hopefully this article has been useful for you to learn how to use calculated in PROC SQL in your SAS code.

Other Articles You'll Also Like:

  • 1.  Date Format ddmmmyyyy in SAS
  • 2.  SAS If Then Statements with Multiple Variables
  • 3.  Using SAS to Find Mean by Group with PROC MEANS
  • 4.  Get the Row Number in a SAS Data Step with _n_
  • 5.  SAS year function – Get Year from Date Variable
  • 6.  yyyy-mm-dd Date Format in SAS
  • 7.  SAS sum() Function – Find Sum Across Columns in Data Step
  • 8.  SAS rename Statement – How to Rename Variables in a Data Step
  • 9.  IN in SAS – Checking if Variable is in Array of Values
  • 10.  SAS yymmdd10. Date Format

About The Programming Expert

The Programming Expert is a compilation of a programmer’s findings in the world of software development, website creation, and automation of processes.

Programming allows us to create amazing applications which make our work more efficient, repeatable and accurate.

At the end of the day, we want to be able to just push a button and let the code do it’s magic.

You can read more about us on our about page.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

About The Programming Expert

the programming expert main image

Welcome to The Programming Expert. We are a group of US-based programming professionals who have helped companies build, maintain, and improve everything from simple websites to large-scale projects.

We built The Programming Expert to help you solve your programming problems with useful coding methods and functions in various programming languages.

Search

Learn Coding from Experts on Udemy

Looking to boost your skills and learn how to become a programming expert?

Check out the links below to view Udemy courses for learning to program in the following languages:

Copyright © 2023 · The Programming Expert · About · Privacy Policy