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;

For indepth knowledge on SAS, click on below