mobileNavlogo
headerlogo

SQL Operators and Joins in SAS

21 September, 2018

Ratings

Related Blogs

  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

About Author
Authorlogo
Name

TekSlate

Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .