SQL in SAS - Part 4

<<< Click here to go back

Theory about statements :

Where Clause
Subsets the output based on specified conditions
When a condition is met (that is, the condition resolves to true), those rows are displayed in the result table; otherwise, no rows are displayed.
Group by Clause
Specifies how to group the data for summarizing.
Having Clause
Subsets grouped data based on specified conditions
Order By Clause 
ASC in order by clause orders the data in ascending order. This is the default order; if neither ASC nor DESC is specified, the data is ordered in ascending order
DESC in order by clause orders the data in descending order

Most of the functions work with PROC SQL, except a few such as LAG, DIF, but there is a logical explanation to that :
Proc SQL work on the whole table at one go, however Data step work row by row; LAG and DIF require row by row operation and hence not applicable in Proc SQL.

Coming to the last piece of the article :

Suppose, we calculate Average of weight and Average of Height of age wise groups separately. Also we we want to calculate the ratio of the calculated averages.

I think you have written this code in you mind already :

Proc SQL;
create table  age_wise as 
select age, mean(height) as avg_h , mean(weight) as avg_w, avg_h / avg_w as ratio
From  sashelp.class 
group by age;
Quit;

Following would be log :

35   Proc SQL;
36   create table  age_wise as
37   select age, mean(height) as avg_h , mean(weight) as avg_w, avg_h / avg_w as ratio
38   From  sashelp.class
39   group by age;
ERROR: The following columns were not found in the contributing tables: avg_h, avg_w.
40   Quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

It is OKAY even by common sense.

So, should I do it in next step ? ... Yes ! You can do that, otherwise you can use "Calculated" option.

Proc SQL;
create table  age_wise as 
select age, mean(height) as avg_h , mean(weight) as avg_w, 
calculated avg_h / calculated avg_w as ratio
From  sashelp.class 
group by age;
Quit;

Hurrah ! You have done it. Are you still not satiated ? Wanna learn more about Proc SQL.

Well, One more important thing you can learn about Proc SQL is joining. As we don't recommend Proc SQL for joining not covering it here. I recommend SQL join only when you need to create Cartesian products. Would be covering Cartesian Product concept wait for my article on "Missing Values Imputation"

Why don't we recommend Proc SQL join, read the following article :

What would you prefer to merge two datasets in SAS : Proc SQL join or Data step merge ?

Enjoy reading our other articles and stay tuned with ...

Kindly do provide your feedback in the 'Comments' Section and share as much as possible.