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

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

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