SAS 9.4 Language Reference, 6th Edition

details, see the documentation for the method that 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 Data Set Options: Reference.

Syntax of WHERE Expression

WHERE Expression Contents

A WHERE expression is a type of SAS expression that defines a condition for selecting

observations. A WHERE expression can be as simple as a single variable name or a

constant (which is a fixed value). A WHERE expression can be a SAS function, or it can

be a sequence of operands and operators that define a condition for selecting

observations. In general, the syntax of a WHERE expression is as follows:

WHERE operand <operator> <operand>

operand

something to be operated on. An operand can be a variable, a SAS function, or a

constant. See “Specifying an Operand” on page 179.

operator

a symbol that requests a comparison, logical operation, or arithmetic calculation. All

SAS expression operators are valid for a WHERE expression, which include

arithmetic, comparison, logical, minimum and maximum, concatenation, parentheses

to control order of evaluation, and prefix operators. In addition, you can use special

WHERE expression operators. These expression operators include BETWEEN-

AND, CONTAINS, IS NULL or IS MISSING, LIKE, sounds-like, and SAME-AND.

See “Specifying an Operator” on page 182.

Specifying an Operand

Variable

A variable is a column in a SAS data set. Each SAS variable has attributes like name and

type (character or numeric). The variable type determines how you specify the value for

which you are searching. For example:

where score > 50;

where date >= '01jan2001'd and time >= '9:00't;

where state = 'Texas';

In a WHERE expression, you cannot use automatic variables created by the DATA step

(for example, FIRST.variable, LAST.variable, _N_, or variables created in assignment

statements).

As in other SAS expressions, the names of numeric variables can stand alone. SAS treats

numeric values of 0 or missing as false; other values as true. In the following example,

the WHERE expression returns all rows where EMPNUM is not missing and not zero

and ID is not missing and not zero:

where empnum and id;

Syntax of WHERE Expression 179

The names of character variables can also stand alone. SAS selects observations where

the value of the character variable is not blank. For example, the following WHERE

expression returns all values not equal to blank:

where lastname;

SAS Function

A SAS function returns a value from a computation or system manipulation. Most

functions use arguments that you supply, but a few obtain their arguments from the

operating environment. To use a SAS function in a WHERE expression, enter its name

and arguments enclosed in parentheses. Some functions that you might want to specify

include:

• SUBSTR extracts a substring.

• TODAY returns the current date.

• PUT returns a given value using a given format.

The following DATA step produces a SAS data set that contains only observations from

data set Customer in which the value of Name begins with Mac and the value of variable

City is

Charleston or Atlanta:

data testmacs;

set customer;

where substr (name,1,3) = 'Mac' and

(city='Charleston' or city='Atlanta');

run;

The OF syntax is permitted in some SAS functions, but it cannot be used when using

those functions that are specified in a WHERE clause. In the following DATA step

example, OF can be used with RANGE.

data abc;

x1=2;

x2=3;

x3=4;

r=range(of x1-x3);

run;

When you use the WHERE clause with RANGE and OF, an error is written to the SAS

log.

Log 11.1 Output When WHERE Clause Is Used with OF

proc print data=abc;

where range(of x1-x3)=6;

--

22

76

ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *,

**,

+, ',', -, /, <, <=, <>, =, >, >=, ?,

AND, BETWEEN, CONTAINS, EQ, GE, GT, LE, LIKE, LT, NE, OR, ^=, |,

||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

run;

Below is a table of SAS functions that can use the OF syntax:

180 Chapter 11 • WHERE-Expression Processing