SQL in SAS - Part 2

<<< Click here to go back

Let's start learning Proc SQL by examples :

Consider following data :  sashelp.class ( it is present in default sashelp library of SAS)

 Case 1

You just want to print and see the data in output window :

Proc print data = sashelp.class;Run;  

or

Proc SQL;
Select * from sashelp.class;
Quit;
                                  
We just learn very basic SQL code in SAS. * here means all the columns; you can specify the column name (a,b,c ...) if you want to select only few columns.

Proc print data = sashelp.class;
var Name Age ;
Run;  

or

Proc SQL;
Select name, age from sashelp.class;
Quit;

Unlike data step, we use "," in between columns' name in Proc SQL. Also, for closing the Proc SQL, we use "Quit". In data step we use "Run";

Remember, often SAS programmer commit the mistake of using RUN in place of RUN, and during execution SAS code keeps on running forever.

You can also limit the observation output using OUTOBS option;

Proc SQL  outobs =5;
select name, age from  sashelp.class; Quit;


Case 2

Let's now create a new table from sashelp.class :

Data New;
Set sashelp.class;
Keep Name Age ;
Run;  

or 

Proc SQL;
Create table  new as 
select  name, age From  sashelp.class;
Quit;

Case 3

Let's now create new / derived variables:

Data New;
Set sashelp.class;
BMI =   Weight/ Height ;
Run;  

or 

Proc SQL;
Create table  new as 
select  *, Weight/ Height  as BMI From  sashelp.class;
Quit;

Do remember to give a name to derived variable with "AS", otherwise would give a temporary name to it. We can also rename parent variable(s) with "AS" option :

Proc SQL;
Create table  new as 
select  name, Sex as gender ,  Age *12 as Age_in_months,
Weight/ Height  as BMI From  sashelp.class;
Quit;

Case 4

Sub-setting the data - Where works in Proc SQL too, If doesn't

Data Female;
Set sashelp.class;
Where sex =  "F" ;
Run;  

or 

Proc SQL;
Create table  Male  as 
select  * From  sashelp.class
where sex = "F" ;
Quit;

Rest of the Where functionalities remain same here :  Refer to Article on Where statement

Similarly, in stead of using if - else if - else in data step , we can use case - when - end statement in Proc SQL. For this read one of our previous articles:

What is more efficient, Data step or Proc SQL ?

Above were few basics about Proc SQL, however the key usage that programmers find in Proc SQL is summarization.

Click here to go next >>>