BETWEEN-AND Operator
The BETWEEN-AND operator is also considered a fully bounded range condition that
selects observations in which the value of a variable falls within an inclusive range of
values.
You can specify the limits of the range as constants or expressions. Any range that you
specify is an inclusive range, so that a value equal to one of the limits of the range is
within the range. The general syntax for using BETWEEN-AND is as follows:
WHERE variable BETWEEN value AND value;
For example:
where empnum between 500 and 1000;
where taxes between salary*0.30 and salary*0.50;
You can combine the NOT logical operator with the BETWEEN-AND operator to select
observations that fall outside the range:
where empnum not between 500 and 1000;
Note: The BETWEEN-AND operator and a fully bounded range condition produce the
same results. That is, the following WHERE expressions are equivalent:
where 500 <= empnum <= 1000;
where empnum between 500 and 1000;
CONTAINS Operator
The most common usage of the CONTAINS (?) operator is to select observations by
searching for a specified set of characters within the values of a character variable. The
position of the string within the variable's values does not matter. However, the operator
is case sensitive when making comparisons.
The following examples select observations having the values Mobay and Brisbayne
for the variable Company, but they do not select observations containing Bayview:
where company contains 'bay';
where company ? 'bay';
You can combine the NOT logical operator with the CONTAINS operator to select
observations that are not included in a specified string:
where company not contains 'bay';
You can also use the CONTAINS operator with two variables, that is, to determine
whether one variable is contained in another. When you specify two variables, keep in
mind the possibility of trailing spaces, which can be resolved using the TRIM function.
proc sql;
select *
from table1 as a, table2 as b
where a.fullname contains trim(b.lastname) and
a.fullname contains trim(b.firstname);
In addition, the TRIM function is helpful when you search on a macro variable.
proc print;
where fullname contains trim("&lname");
run;
184 Chapter 11 • WHERE-Expression Processing
IS NULL or IS MISSING Operator
The IS NULL or IS MISSING operator selects observations in which the value of a
variable is missing. The operator selects observations with both regular or special
missing value characters and can be used for both character and numeric variables.
where idnum is missing;
where name is null;
The following are equivalent for character data:
where name is null;
where name = ' ';
And the following is equivalent for numeric data. This statement differentiates missing
values with special missing value characters:
where idnum <= .Z;
You can combine the NOT logical operator with IS NULL or IS MISSING to select
nonmissing values, as follows:
where salary is not missing;
LIKE Operator
The LIKE operator selects observations by comparing the values of a character variable
to a specified pattern, which is referred to as pattern matching. The LIKE operator is
case sensitive. There are two special characters available for specifying a pattern:
percent sign (%)
specifies that any number of characters can occupy that position. The following
WHERE expression selects all employees with a name that starts with the letter N.
The names can be of any length.
where lastname like 'N%';
underscore (_)
matches just one character in the value for each underscore character. You can
specify more than one consecutive underscore character in a pattern, and you can
specify a percent sign and an underscore in the same pattern. For example, you can
use different forms of the LIKE operator to select character values from this list of
first names:
• Diana
• Diane
• Dianna
• Dianthus
• Dyan
The following table shows which of these names is selected by using various forms of
the LIKE operator:
Pattern Name Selected
like 'D_an' Dyan
like 'D_an_' Diana, Diane
like 'D_an__' Dianna
Syntax of WHERE Expression 185
Pattern Name Selected
like 'D_an%' all names from list
You can use a SAS character expression to specify a pattern, but you cannot use a SAS
character expression that uses a SAS function.
You can combine the NOT logical operator with LIKE to select values that do not have
the specified pattern, such as the following:
where frstname not like 'D_an%';
Because the % and _ characters have special meaning for the LIKE operator, you must
use an escape character when searching for the % and _ characters in values. An escape
character is a single character that, in a sequence of characters, signifies that what
follows takes an alternative meaning. For the LIKE operator, an escape character
signifies to search for literal instances of the % and _ characters in the variable's values
instead of performing the special-character function.
For example, if the variable X contains the values abc, a_b, and axb, the following
LIKE operator with an escape character selects only the value
a_b. The escape character
(/) specifies that the pattern searches for a literal ' _' that is surrounded by the characters
a and b. The escape character (/) is not part of the search.
where x like 'a/_b' escape '/';
Without an escape character, the following LIKE operator would select the values a_b
and axb. The special character underscore in the search pattern matches any single b
character, including the value with the underscore:
where x like 'a_b';
To specify an escape character, include the character in the pattern-matching expression,
and then the keyword ESCAPE followed by the escape-character expression. When you
include an escape character, the pattern-matching expression must be enclosed in
quotation marks, and it cannot contain a column name. The escape-character expression
evaluates to a single character. The operands must be character or string literals. If it is a
single character, enclose it in quotation marks.
LIKE 'pattern-matching-expression' ESCAPE 'escape-character-expression'
Sounds-like Operator
The sounds-like ( =*) operator selects observations that contain a spelling variation of a
specified word or words. The operator uses the Soundex algorithm to compare the
variable value and the operand. For more information, see the SOUNDEX function in
SAS Functions and CALL Routines: Reference.
Note: Note that the SOUNDEX algorithm is English-biased, and is less useful for
languages other than English.
Although the sounds-like operator is useful, it does not always select all possible values.
For example, consider that you want to select observations from the following list of
names that sound like Smith:
• Schmitt
• Smith
• Smithson
• Smitt
186 Chapter 11 • WHERE-Expression Processing
• Smythe
The following WHERE expression selects all the names from this list except
Smithson:
where lastname=* 'Smith';
You can combine the NOT logical operator with the sounds-like operator to select values
that do not contain a spelling variation of a specified word or words, such as:
where lastname not =* 'Smith';
Note: The sounds-like operator cannot be optimized with an index.
SAME-AND Operator
Use the SAME-AND operator to add more conditions to an existing WHERE expression
later in the program without retyping the original conditions. This capability is useful
with the following:
• interactive SAS procedures
• full-screen SAS procedures that enable you to enter a WHERE expression on the
command line
• any type of RUN-group processing
Use the SAME-AND operator when you already have a WHERE expression defined and
you want to insert additional conditions. The SAME-AND operator has the following
form:
• where-expression-1;
• ... SAS statements...
• WHERE SAME AND where-expression-2;
• ... SAS statements...
• WHERE SAME AND where-expression-n;
SAS selects observations that satisfy the conditions after the SAME-AND operator in
addition to any previously defined conditions. SAS treats all of the existing conditions as
if they were conditions separated by AND operators in a single WHERE expression.
The following example shows how to use the SAME-AND operator within RUN groups
in the GPLOT procedure. The SAS data set YEARS has three variables and contains
quarterly data for the 2009–2011 period:
proc gplot data=years;
plot unit*quar=year;
run;
where year > '01jan2009'd;
run;
where same and year < '01jan2012'd;
run;
The following WHERE expression is equivalent to the preceding code:
where year > ''01jan2009'd and year < '01jan2012'd;
Syntax of WHERE Expression 187
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.