SQL Operators and Joins in SAS

  Operators: Using operators(SQL), we can add the tables for reporting. Different types in SAS are

1. Union all
2. Union
3. Intersect
4. Except

Ex: Data lab1; Input stno test $ units; Date = ‘12jan2003’d; Format date date9.; Cards; 100  hr  78 101  hr  90 100  dbp 89 100  sbp 156 101  dbp 90 101  sbp  178 ; Data lab2; Input stno test $ units; Date = ‘13jan2003’d; Format date date9.; Cards; 102  hr  78 103  hr  90 103  dbp 89 103  sbp 156 102  dbp 90 102  sbp  178 ; Order for reporting Ex: Proc sql; Select * from lab1; Union all Select * from lab2; Quit;    

Union:

It can be used to add report in sorting order (ascending order) for reporting. Ex: Proc SQL; Select * from lab1 Union Select * from lab2; Quit;

• Union operator defaults takes starting variables in report as a sorting variable.

 Desired to gain proficiency on SAS? Explore the blog post on SAS Training Online to become a pro in SAS.

Intersect:

It can be used to report common observations from required data sets. Ex: Data ex; Input stno ad $ date :date 9.; Format date date 9; Cards; 100    Eyedis  12jan2003 105   Eardis  12jan2003 102   Eardis  12jan2003 ; Data unex ; Input stno ad $ date :date 9.; Format date :date 9; Cards; 104   comma       12jam2003 103   nervous pro 12jan2003 105   Eardis       12jan2006 ; Proc sql; Select * from ex Intersect Select * from unex; Quit; Note: Union all report with common observation. Union report without common observation(duplicate).  

Difference between duplicate and common observation:

Common observation means observation available in one or more data set. If we check these data sets individually then common observation is a valid observation. If we check both at a time then common observation is a invalid observation(duplicate observation).  

Except:

It can be used to report the data from the required data set observations. Ex: Proc sql; Select * from ex Except Select * from unex; Quit; /* To report the data from the required data sets without common observations*/ Ex: Proc sql; (select * from ex Except Select * from unex Union (select * from unex Except Select * from ex); Quit;  

Joins:

Using joins we can combine the reports based on matching variables. Different types, they are:

1. Simple join
2. Inner join
3. Outer join
4. Natural join

Simple joins:

Using simple joins, We can report matching observations from the required data sets. Ex: Data exadevent; Input stno exad $ exdate :date 9.; Format exdate date 9; Cards; 230    Eyedis  12feb2005 456   skinprb  15feb2005 345    cold    16mar2005 ; Data unexadevent ; Input stno unexad $ unexadate :date 9.; Format unexdate date 9; Cards; 230      Nervous    28feb2005 156   Earddis     17mar2005 145   diabetis       18mar2005 ; Proc sql; Select * from exadevent as ex, Unexadevent as unex where Ex.stno = unex .stno; Quit;  

Table alias:

Using this concept, we can create alternate name of the table or required application temporarily.

• Simple join concept can be used between the multiple tables at a time.

Inner join:

     Works like a simple join, but inner join can be used between 2 tables

On clause:

It can be used instead of where clause for condition.

• Inner join can be activated with inner join

Ex: Proc sql; Select * from exadevent as ex Inner join Unexadevent as unex on Ex.stno = unex .stno; Quit;

Outer join:

   3 types

1. Left join
2. Right join
3. Full join

Left join:

    Reports all observation from left side table and only matching observation and conditions based right side table. Ex: Proc sql; Select * from exadevent as ex Left join Unexadevent as unex on Ex.stno = unex .stno; Quit;  

Right join:

Reports all observation from right  side table and only matching observation and conditions based left side table. Ex: Proc sql; Select * from exadevent as ex Right join Unexadevent as unex on Ex.stno = unex .stno; Quit;  

Full join:

   Reports all observation from 2 tables match the rows. Ex: Proc sql; Select * from exadevent as ex full join Unexadevent as unex on Ex.stno = unex .stno; Quit;  

Natural join:

We can report matching observation from required data set without using any condition. Ex: Proc sql; Select * from exadevent Natural join Unexadevent; Quit;  

Self join:

If we join the table internally with same table, then it is called self join. Ex: Data trt; Input stno Bsbp drug $ Asbp; Cards; 190  167   col5mg  178 123  178   col5mg  167 198  167   col10mg  146 237  172   col10mg  134 ; Proc sql; Select * from trt Where Bsbp > Asbp; Quit.