O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th 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

Using Other Conditional Operators
Overview of Using Other Conditional Operators
You can use many different conditional operators in a WHERE clause. The following
table lists other operators that you can use:
Table 2.4 Conditional Operators
Operator Definition Example
ANY specifies that at least one
of a set of values
obtained from a
subquery must satisfy a
given condition
where Population > any
(select Population from
sql.countries)
ALL specifies that all of the
values obtained from a
subquery must satisfy a
given condition
where Population > all
(select Population from
sql.countries)
BETWEEN-AND tests for values within an
inclusive range
where Population between
1000000 and 5000000
CONTAINS tests for values that
contain a specified string
where Continent contains
'America';
EXISTS tests for the existence of
a set of values obtained
from a subquery
where exists (select *
from sql.oilprod);
IN tests for values that
match one of a list of
values
where Name in ('Africa',
'Asia');
IS NULL or IS
MISSING
tests for missing values
where Population is
missing;
LIKE tests for values that
match a specified
pattern
1
where Continent like 'A
%';
=* tests for values that
sound like a specified
value
where Name =* 'Tiland';
Note: All of these operators can be prefixed with the NOT operator to form a negative
condition.
1
You can use a percent sign (%) to match any number of characters. You can use an underscore (_) to match one arbitrary character.
Retrieving Rows That Satisfy a Condition 49
Using the IN Operator
The IN operator enables you to include values within a list that you supply. The
following example uses the IN operator to include only the mountains and waterfalls in
the Sql.Features table:
libname sql 'SAS-library';
proc sql outobs=12;
title 'World Mountains and Waterfalls';
select Name, Type, Height format=comma10.
from sql.features
where Type in ('Mountain', 'Waterfall')
order by Height;
Output 2.25 Using the IN Operator
Using the IS MISSING Operator
The IS MISSING operator enables you to identify rows that contain columns with
missing values. The following example selects countries that are not located on a
continent. That is, these countries have a missing value in the Continent column:
proc sql;
title 'Countries with Missing Continents';
select Name, Continent
from sql.countries
where Continent is missing;
Note: The IS NULL operator is the same as, and interchangeable with, the IS MISSING
operator.
50 Chapter 2 Retrieving Data from a Single Table
Output 2.26 Using the IS MISSING Operator
Using the BETWEEN-AND Operators
To select rows based on a range of values, you can use the BETWEEN-AND operators.
This example selects countries that have latitudes within five degrees of the Equator:
proc sql outobs=12;
title 'Equatorial Cities of the World';
select City, Country, Latitude
from sql.worldcitycoords
where Latitude between -5 and 5;
Note: In the tables used in these examples, latitude values that are south of the Equator
are negative. Longitude values that are west of the Prime Meridian are also negative.
Note: Because the BETWEEN-AND operators are inclusive, the values that you specify
in the BETWEEN-AND expression are included in the results.
Retrieving Rows That Satisfy a Condition 51
Output 2.27 Using the BETWEEN-AND Operators
Using the LIKE Operator
The LIKE operator enables you to select rows based on pattern matching. For example,
the following query returns all countries in the Sql.Countries table that begin with the
letter Z and are any number of characters long, or end with the letter a and are five
characters long:
libname sql 'SAS-library';
proc sql;
title1 'Country Names that Begin with the Letter "Z"';
title2 'or Are 5 Characters Long and End with the Letter "a"';
select Name
from sql.countries
where Name like 'Z%' or Name like '____a';
52 Chapter 2 Retrieving Data from a Single Table
Output 2.28 Using the LIKE Operator
The percent sign (%) and underscore (_) are wildcard characters. For more information
about pattern matching with the LIKE comparison operator, see Chapter 7, “SQL
Procedure,” on page 215.
Using Truncated String Comparison Operators
Truncated string comparison operators are used to compare two strings. They differ from
conventional comparison operators in that, before executing the comparison, PROC SQL
truncates the longer string to be the same length as the shorter string. The truncation is
performed internally; neither operand is permanently changed. The following table lists
the truncated comparison operators:
Table 2.5 Truncated String Comparison Operators
Symbol Definition Example
EQT equal to truncated strings
where Name eqt 'Aust';
GTT greater than truncated strings
where Name gtt 'Bah';
LTT less than truncated strings
where Name ltt 'An';
GET greater than or equal to truncated strings
where Country get
'United A';
LET less than or equal to truncated strings
where Lastname let
'Smith';
Retrieving Rows That Satisfy a Condition 53

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