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

The following example sorts the rows in the Continents table by the LowPoint column:

libname sql 'SAS-library';

proc sql;

title 'Continents, Sorted by Low Point';

select Name, LowPoint

from sql.continents

order by LowPoint;

Because three continents have a missing value in the LowPoint column, those continents

appear first in the output. Note that because the query does not specify a secondary sort,

rows that have the same value in the LowPoint column, such as the first three rows of

output, are not displayed in any particular order. In general, if you do not explicitly

specify a sort order, then PROC SQL output is not guaranteed to be in any particular

order.

Output 2.21 Sorting Columns That Contain Missing Values

Retrieving Rows That Satisfy a Condition

The WHERE clause enables you to retrieve only rows from a table that satisfy a

condition. WHERE clauses can contain any of the columns in a table, including columns

that are not selected.

Using a Simple WHERE Clause

The following example uses a WHERE clause to find all countries that are in the

continent of Europe and their populations:

libname sql 'SAS-library';

proc sql outobs=12;

44 Chapter 2 • Retrieving Data from a Single Table

title 'Countries in Europe';

select Name, Population format=comma10.

from sql.countries

where Continent = 'Europe';

Output 2.22 Using a Simple WHERE Clause

Retrieving Rows Based on a Comparison

You can use comparison operators in a WHERE clause to select different subsets of data.

The following table lists the comparison operators that you can use:

Table 2.2 Comparison Operators

Symbol

Mnemonic

Equivalent Definition Example

= EQ equal to

where Name =

'Asia';

^= or ~= or ¬= or <> NE not equal to

where Name ne

'Africa';

> GT greater than

where Area >

10000;

< LT less than

where Depth <

5000;

Retrieving Rows That Satisfy a Condition 45

Symbol

Mnemonic

Equivalent Definition Example

>= GE greater than or equal

to

where

Statehood >=

'01jan1860'd;

<= LE less than or equal to

where

Population <=

5000000;

The following example subsets the Sql.UnitedStates table by including only states with

populations greater than 5,000,000 people:

libname sql 'SAS-library';

proc sql;

title 'States with Populations over 5,000,000';

select Name, Population format=comma10.

from sql.unitedstates

where Population gt 5000000

order by Population desc;

46 Chapter 2 • Retrieving Data from a Single Table

Output 2.23 Retrieving Rows Based on a Comparison

Retrieving Rows That Satisfy Multiple Conditions

You can use logical, or Boolean, operators to construct a WHERE clause that contains

two or more expressions. The following table lists the logical operators that you can use:

Table 2.3 Logical (Boolean) Operators

Symbol

Mnemonic

Equivalent Definition Example

& AND specifies that both the

previous and following

conditions must be true

Continent =

'Asia' and

Population >

5000000

Retrieving Rows That Satisfy a Condition 47

Symbol

Mnemonic

Equivalent Definition Example

! or | or ¦ OR specifies that either the

previous or the

following condition

must be true

Population <

1000000 or

Population >

5000000

^ or ~ or ¬ NOT specifies that the

following condition

must be false

Continent not

'Africa'

The following example uses two expressions to include only countries that are in Africa

and that have a population greater than 20,000,000 people:

libname sql 'SAS-library';

proc sql;

title 'Countries in Africa with Populations over 20,000,000';

select Name, Population format=comma10.

from sql.countries

where Continent = 'Africa' and Population gt 20000000

order by Population desc;

Output 2.24 Retrieving Rows That Satisfy Multiple Conditions

Note: You can use parentheses to improve the readability of WHERE clauses that

contain multiple, or compound, expressions, such as the following:

where (Continent = 'Africa' and Population gt 2000000) or

(Continent = 'Asia' and Population gt 1000000)

48 Chapter 2 • Retrieving Data from a Single Table