SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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