SAS calculated – Use Columns Created from Select in PROC SQL

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.