WHERE-Expression Processing

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 details, see the documentation for the method you are using to specify a WHERE expression.

Note: By default, a WHERE expression does not evaluate added and modified observations. To specify whether a WHERE expression should evaluate updates, you can specify the WHEREUP= data set option. See the WHEREUP= data set option in SAS Language Reference: Dictionary. [cautionend]

Top of Page