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
Table 11.1 SAS Functions That Use the OF Syntax
CAT
CATS
CATT
CATX
CSS
CV
GEOMEAN
GEOMEANZ
HARMEAN
HARMEANZ
KURTOSIS
MAX
MEAN
MIN
N
NMISS
ORDINAL
RANGE
RMS
SKEWNESS
STD
STDERR
SUM
USS
VAR
Note: The SAS functions that are used in a WHERE expression and can be optimized
by an index are the SUBSTR function and the TRIM function.
For more information about SAS functions, see SAS Functions and CALL Routines:
Reference.
Constant
A constant is a fixed value such as a number or quoted character string, that is, the value
for which you are searching. A constant is a value of a variable obtained from the SAS
data set, or values created within the WHERE expression itself. Constants are also called
literals. For example, a constant could be a flight number or the name of a city. A
constant can also be a time, date, or datetime value.
The value is either numeric or character. Note the following rules regarding whether to
use quotation marks:
• If the value is numeric, do not use quotation marks.
where price > 200;
• If the value is character, use quotation marks.
where lastname eq 'Martin';
• You can use either single or double quotation marks, but do not mix them. Quoted
values must be exact matches, including case.
• It might be necessary to use single quotation marks when double quotation marks
appear in the value, or use double quotation marks when single quotation marks
appear in the value.
where item = '6" decorative pot';
where name ? "D'Amico";
• A SAS date constant must be enclosed in quotation marks. When you specify date
values, case is not important. You can use single or double quotation marks. The
following expressions are equivalent:
where birthday = '24sep1975'd;
where birthday = '24sep1975"d;
Syntax of WHERE Expression 181
Specifying an Operator
Arithmetic Operators
Arithmetic operators enable you to perform a mathematical operation. The arithmetic
operators include the following:
Table 11.2 Arithmetic Operators
Symbol Definition Example
* multiplication where bonus = salary * .10;
/ division where f = g/h;
+ addition where c = a+b;
- subtraction where f = g-h;
** exponentiation where y = a**2;
Comparison Operators
Comparison operators (also called binary operators) compare a variable with a value or
with another variable. Comparison operators propose a relationship and ask SAS to
determine whether that relationship holds. For example, the following WHERE
expression accesses only those observations that have the value 78753 for the numeric
variable ZipCode:
where zipcode eq 78753;
The following table lists the comparison operators:
Table 11.3 Comparison Operators
Symbol
Mnemonic
Equivalent Definition Example
= EQ equal to where empnum eq 3374;
^= or ~= or ¬=
or <>
NE not equal to where status ne full-time;
> GT greater than where hiredate gt
'01jun1982'd;
< LT less than where empnum < 2000;
>= GE greater than or equal to where empnum >= 3374;
<= LE less than or equal to where empnum <= 3374;
182 Chapter 11 • WHERE-Expression Processing
Symbol
Mnemonic
Equivalent Definition Example
IN equal to one from a list of
values
where state in
('NC','TX');
When you do character comparisons, you can use the colon (:) modifier to compare only
a specified prefix of a character string. For example, in the following WHERE
expression, the colon modifier, used after the equal sign, tells SAS to look at only the
first character in the values for variable LastName and to select the observations with
names beginning with the letter S:
where lastname=: 'S';
Note that in the SQL procedure, the colon modifier that is used in conjunction with an
operator is not supported; you can use the LIKE operator instead.
IN Operator
The IN operator, which is a comparison operator, searches for character and numeric
values that are equal to one from a list of values. The list of values must be in
parentheses, with each character value in quotation marks and separated by either a
comma or blank.
For example, suppose you want all sites that are in North Carolina or Texas. You could
specify:
where state = 'NC' or state = 'TX';
However, it is easier to use the IN operator, which selects any state in the list:
where state in ('NC','TX');
In addition, you can use the NOT logical operator to exclude a list.
where state not in ('CA', 'TN', 'MA');
You can use a shorthand notation to specify a range of sequential integers to search. The
range is specified by using the syntax M:N as a value in the list to search, where M is the
lower bound and N is the upper bound. M and N must be integers, and M, N, and all the
integers between M and N are included in the range. For example, the following
statements are equivalent.
• y = x in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
• y = x in (1:10);
Fully Bounded Range Condition
A fully bounded range condition consists of a variable between two comparison
operators, specifying both an upper and lower limit. For example, the following
expression returns the employee numbers that fall within the range of 500 to 1000
(inclusive):
where 500 <= empnum <= 1000;
Note that the previous range condition expression is equivalent to the following:
where empnum >= 500 and empnum <= 1000;
You can combine the NOT logical operator with a fully bounded range condition to
select observations that fall outside the range. Note that parentheses are required:
where not (500 <= empnum <= 1000);
Syntax of WHERE Expression 183
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.