O'Reilly logo

SAS 9.4 Language Reference, 6th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required