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;
   title 'Countries in Europe';
   select Name, Population format=comma10.
      from sql.countries
      where Continent = 'Europe';
Using a Simple WHERE Clause
Countries in Europe

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:
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;
>=
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;
Retrieving Rows Based on a Comparison
States with Populations over 5,000,000

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:
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
! 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;
Retrieving Rows That Satisfy Multiple Conditions
Countries in Africa with Populations over 20,000,000
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)

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:
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 (footnote1)
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.

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;
Using the IN Operator
World Mountains and Waterfalls

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.
Using the IS MISSING Operator
Countries with Missing Continents

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.
Using the BETWEEN-AND Operators
Equatorial Cities of the World

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';
Using the LIKE Operator
Country Names that Begin with the Letter "Z" or Are 5 Characters Long and End with the Letter "a"
The percent sign (%) and underscore (_) are wildcard characters. For more information about pattern matching with the LIKE comparison operator, see SQL Procedure.

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:
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';
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 ';
Using a Truncated String Comparison Operator
"New" U.S. States

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;
Using a WHERE Clause with Missing Values (Incorrect Output)
World Features with a Depth of Less than 500 Feet
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;
Using a WHERE Clause with Missing Values (Corrected Output)
World Features with a Depth of Less than 500 Feet
FOOTNOTE 1:You can use a percent symbol (%) to match any number of characters. You can use an underscore (_) to match one arbitrary character.[return]
Copyright © SAS Institute Inc. All rights reserved.