SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Symbol Definition Example

NET not equal to truncated strings

where Style net 'TWO';

The following example returns a list of U.S. states that have 'New ' at the beginning of

their names:

proc sql;

title '"New" U.S. States';

select Name

from sql.unitedstates

where Name eqt 'New ';

Output 2.29 Using a Truncated String Comparison Operator

Using a WHERE Clause with Missing Values

If a column that you specify in a WHERE clause contains missing values, then a query

might provide unexpected results. For example, the following query returns all features

from the Sql.Features table that have a depth of less than 500 feet:

libname sql 'SAS-library';

/* incorrect output */

proc sql outobs=12;

title 'World Features with a Depth of Less than 500 Feet';

select Name, Depth

from sql.features

where Depth lt 500

order by Depth;

54 Chapter 2 • Retrieving Data from a Single Table

Output 2.30 Using a WHERE Clause with Missing Values (Incorrect Output)

However, because PROC SQL treats missing values as smaller than nonmissing values,

features that have no depth listed are also included in the results. To avoid this problem,

you could adjust the WHERE expression to check for missing values and exclude them

from the query results, as follows:

libname sql 'SAS-library';

/* corrected output */

proc sql outobs=12;

title 'World Features with a Depth of Less than 500 Feet';

select Name, Depth

from sql.features

where Depth lt 500 and Depth is not missing

order by Depth;

Retrieving Rows That Satisfy a Condition 55