SQL in SAS - Part 3

<<< Click here to go back

Starting the summarization functionality of PROC SQL.

Let's calculate average height and maximum of weight from sashelp.class :

Proc SQL;
create table  summary_1 as 
select mean(height) as avg_h, max(weight) as max_w
From  sashelp.class ;
Quit;

We can use various summarization function such as :
sum for summation , min for minimum, std for standard deviation, count for counting 

We can use where statement simultaneously; let summarize data for male students:

Proc SQL;
create table  summary_1 as 
select mean(height) as avg_h, max(weight) as max_w
From  sashelp.class 
where sex = "M";
Quit;

Also there is a very special option "Distinct" available in Proc SQL. Suppose we need to know "how many unique age are there in the data", we can do it by :

Proc SQL;
select count( distinct age) as count_unique_age
From  sashelp.class ;
Quit;

Result would be 6.

And the last piece of Proc SQL statement is "order by", which helps in sorting the result data in the required order.

Proc SQL;
create table  age_wise as 
select age, mean(height) as avg_h 
From  sashelp.class 
where sex = "M"
order by avg_h   Desc;
Quit;

desc option is used to define descending order, if we don't provide this option, default order is ascending.




We are not explaining HAVING statement in this article ... why ? We have written a descriptive article on HAVING, contrasting it with WHERE statement already.

What is the difference between the "Where" and "Having" statements in Proc SQL ? 

Click here to go next >>>