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

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

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