• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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

Summary
Review Date
Reviewed Item
SQL Operators and Joins in SAS
Author Rating
4

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SQL Operators and Joins in SAS"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.