SAS 9.4 Language Reference, 6th Edition

Or you can have multiple WHERE expressions, referred to as a compound

expression, such as the following:

where sales gt 600000 and salary lt 100000;

Where to Use a WHERE Expression

In SAS, you can use a WHERE expression in the following situations:

• WHERE statement in both DATA and PROC steps. For example, the following

PRINT procedure includes a WHERE statement so that only the observations where

the year is greater than 2001 are printed:

proc print data=employees;

where startdate > '01jan2001'd;

run;

• WHERE= data set option. The following PRINT procedure includes the WHERE=

data set option:

proc print data=employees (where=(startdate > '01jan2001'd));

run;

• WHERE clause in the SQL procedure, SCL, and SAS/IML software. For example,

the following SQL procedure includes a WHERE clause to select only the states

where the murder count is greater than seven:

proc sql;

select state from crime

where murder > 7;

• WHERE command in windowing environments like SAS/FSP software:

where age > 15

• SAS view (DATA step view, SAS/ACCESS view, PROC SQL view), stored with the

definition. For example, the following SQL procedure creates an SQL view named

STAT from the data file Crime and defines a WHERE expression for the SQL view

definition:

proc sql;

create view stat as

select * from crime

where murder > 7;

In some cases, you can combine the methods that you use to specify a WHERE

expression. That is, you can use a WHERE statement as follows:

• in conjunction with a WHERE= data set option

• along with the WHERE= data set option in windowing procedures, and in

conjunction with the WHERE command

• on a SAS view that has a stored WHERE expression

For example, it might be useful to combine methods when you merge data sets. That is,

you might want different criteria to apply to each data set when you create a subset of

data. However, when you combine methods to create a subset of data, there are some

restrictions. For example, in the DATA step, if a WHERE statement and a WHERE=

data set option apply to the same data set, the data set option takes precedence. For

178 Chapter 11 • WHERE-Expression Processing