Chapter Contents |
Previous |
Next |
WHERE |
Valid: | in DATA and PROC steps |
Category: | Action |
Type: | Declarative |
Syntax |
WHERE where-expression-1
< logical-operator where-expression-n>; |
Tip: | The operands and operators described in the next several sections are also valid for the WHERE= data set option. |
Tip: | You can specify multiple where-expressions. |
Details |
Using the WHERE statement may improve the efficiency of your SAS programs because SAS is not required to read all observations from the input data set.
The WHERE statement cannot be executed conditionally; that is, you cannot use it as part of an IF-THEN statement.
WHERE statements can contain multiple WHERE expressions that are joined by logical operators.
Note: Using indexed SAS data sets can significantly
improve performance when you use WHERE expressions to access a subset of the
observations in a SAS data set. For more information about indexes, see "Indexes"
in the "SAS Data Files" chapter of
SAS Language Reference: Concepts for a complete discussion of
WHERE-expression processing with indexed data sets and a list of guidelines
to consider before you index your SAS data sets.
The WHERE statement applies to all data sets in the preceding SET, MERGE, MODIFY, or UPDATE statement, and variables that are used in the WHERE statement must appear in all of those data sets. You cannot use the WHERE statement with programming statements that select observations by observation number, such as the OBS= data set option and the POINT= option in the SET and MODIFY statements. When you use the WHERE statement, the FIRSTOBS= data set option must be 1. You cannot use the WHERE statement to select records from an external file that contains raw data, nor can you use the WHERE statement within the same DATA step in which you read in-stream data with a CARDS or DATALINES statement.
For each iteration of the DATA step, the first operation
the SAS System performs in each execution of a SET, MERGE, MODIFY, or UPDATE
statement is to determine whether the observation in the input data set meets
the condition of the WHERE statement. The WHERE statement takes effect immediately
after the input data set options are applied and before any other statement
in the DATA step is executed. If a DATA step combines observations using a
WHERE statement with a MERGE, MODIFY, or UPDATE statement, SAS selects observations
from each input data set before it combines them.
If a DATA step contains both a WHERE statement and a BY statement, the WHERE statement executes before BY groups are created. Therefore, BY groups reflect groups of observations in the subset of observations that are selected by the WHERE statement, not the actual BY groups of observations in the original input data set.
For a complete discussion of BY-group processing, see "BY-Group
Processing" in
SAS Language Reference: Concepts.
You can use
the WHERE statement with any SAS procedure that reads a SAS data set. The
WHERE statement is useful for subsetting the original data set for processing
by the procedure. The
SAS Procedures Guide documents the action of the WHERE statement
only in those procedures for which you can specify more than one data set.
In all other cases, the WHERE statement performs as documented here.
A DATA or PROC step attempts to use an available index to optimize the selection of data when an indexed variable is used in combination with one of the following:
SUBSTR requires the following arguments:
where substr(variable,position,length) ='character-string';An index is used in processing when the arguments of the SUBSTR function meet all of the following conditions:
Operands include
You cannot use variables that are created within the DATA step (for example, FIRST.variable, LAST.variable, _N_, or variables that are created in assignment statements) in a WHERE expression because the WHERE statement is executed before the SAS System brings observations into the DATA or PROC step. When WHERE expressions contain comparisons, the unformatted values of variables are compared.
Use operands in WHERE statements as in the following examples:
As in other SAS expressions, the names of numeric variables can stand alone. SAS treats values of 0 or missing as false; other values are true. These examples are WHERE expressions that contain the numeric variables EMPNUM and SSN:
Character literals or the names of character variables
can also stand alone in WHERE expressions. If you use the name of a character
variable by itself as a WHERE expression, SAS selects observations where the
value of the character variable is not blank.
You can include both SAS operators and special WHERE expression operators in the WHERE statement. For a complete list of the operators, see WHERE Statement Operators. For the rules SAS follows when it evaluates WHERE expressions, see "WHERE Processing" in SAS Language Reference: Concepts.
Operator Type | Symbol or Mnemonic | Description | |
---|---|---|---|
Arithmetic | |||
* | multiplication | ||
/ | division | ||
+ | addition | ||
- | subtraction | ||
** | exponentiation | ||
Comparison | |||
= or EQ | equal to | ||
ˆ=, <>, or NE (table note 1) | not equal to | ||
> or GT | greater than | ||
< or LT | less than | ||
>= or GE | greater than or equal to | ||
<= or LE | less than or equal to | ||
IN | equal to one of a list | ||
Logical (Boolean) | |||
& or AND | logical and | ||
| or OR (table note 2) | logical or | ||
~,^ , ¬, or NOT (table note 3) | logical not | ||
Other | |||
|| (table note 4) | concatenation of character variables | ||
( ) | indicate order of evaluation | ||
+ prefix | positive number | ||
- prefix | negative number | ||
WHERE Expression Only | |||
BETWEEN-AND | an inclusive range | ||
? or CONTAINS | a character string | ||
IS NULL or IS MISSING | missing values | ||
LIKE | match patterns | ||
=* | sounds-like | ||
SAME-AND | add clauses to an existing WHERE statement without retyping original one |
The caret (^), tilde (~), and the not sign (¬ ) all indicate a logical not. Use the character available on your keyboard, or use the mnemonic equivalent.
The OR symbol ( | ), broken vertical bar ( | ), and exclamation point (!) all indicate a logical or. Use the character available on your keyboard, or use the mnemonic equivalent.
The caret (^), tilde (~), and the not sign (¬ ) all indicate a logical not. Use the character available on your keyboard, or use the mnemonic equivalent.
Two OR
symbols (| | ), two broken vertical bars ( | | ), or two exclamation points
(!!) indicate concatenation. Use the character available on your keyboard.
You can use the colon modifier (:) with any of the comparison operators. See SAS Operators for more information about the colon modifier.
Comparisons |
Examples |
This DATA step produces a SAS data set that contains only observations from
data set CUSTOMER in which the value for NAME begins with
Mac
and the value for CITY is
Charleston
or
Atlanta
.
data testmacs; set customer; where substr(name,1,3)='Mac' and (city='Charleston' or city='Atlanta'); run;
where empnum between 500 and 1000;
where company ? 'bay'; where company contains 'bay';
where name is null; where name is missing;
where name like 'D%';
Diana | |
Diane | |
Dianna | |
Dianthus | |
Dyan |
WHERE Statement | Name Selected |
---|---|
where name like 'D_an'; |
Dyan |
where name like 'D_an_'; |
Diana, Diane |
where name like 'D_an__'; |
Dianna |
where name like 'D_an%'; |
all names from list |
where lastname=*'Smith';
where year>1991; ...more SAS statements... where same and year<1999;
In this example, the second WHERE statement is equivalent to the following WHERE statement:
where year>1991 and year<1999;
See Also |
Data Set Option:
| |||
Statement:
| |||
SAS Guide to the SQL Procedure: Usage and Reference | |||
SAS/IML Software: Usage and Reference | |||
SAS Procedures Guide | |||
"SAS Indexes" in SAS Language Reference: Concepts | |||
"WHERE Processing" in SAS Language Reference: Concepts | |||
"BY-Group Processing" in SAS Language Reference: Concepts | |||
Beatrous, S. & Clifford, W. (1998), "Sometimes You Do Get What You Want: SAS I/O Enhancements in Version 7," Proceedings of the Twenty-third Annual SAS Users Group International Conference, 23. |
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.