Proc SQL With Where Clause In SAS

We can have a look into WHERE clause in SQL Procedure. It helps us to dig the specific data but it could be faster and useful if we use WHERE Clause with operators.

Suppose we have a SAS dataset and variable of it as follows: Date, Name,LName Address, State

e.g: Name =SHANKER,  LName=SAINI

To select data from any range we can use BETWEEN – AND operator with Where clause in PROC SQL.

• Syntax: Where Date between ‘01Jul2014’d AND ‘28Jul2014’d

We can use IN operator to select values from list:

• Syntax: WHERE State in (‘MH’,’UP’,’PB’)

We can use MISSING operator to select missing values from data:

• Syntax: Where State is MISSING

We can use CONTAINS operator to select a string by using its letters only:

• Syntax: Where Name CONTAINS ‘KER’

 We can use LIKE operator with WHERE Clause with PROC SQL to select any pattern:

• Syntax:  Where name like ’K_R’

1. We have two wild card operators with WHERE clause ‘_’ (Underscore) and ‘%’ (Percent)


2. With the Like operator in WHERE Clause , Underscore(_) wild card is for one character and percent ‘%’ sign is for more than one character missing in string

• Syntax : Where Name like ‘%ER’

We can use (*) to select the data with spelling variation or for values that sound like specified values like:   Syntax : Where LName=*’SAINI’

We can use a column reference / alias in WHERE Clause to refer to a calculated value (We have to use CALCULATED keyword in this case).

 PROC SQL;

Select Var1, Var2, Var3 + Var4 as Total, CALCULATED Total/4 as Quarter from DSN; QUIT;

Or

PROC SQL;

Select Var1, Var2, Var3 + Var4 as Total from DSN

Where CALCULATER Total  > 500;      or      Where Var3 + Var4 > 500 ;  QUIT;