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

Output 2.10 Referring to a Calculated Column by Alias

Note: Because this query sets a numeric format of 4.1 on the HighC, LowC, and Range

columns, the values in those columns are rounded to the nearest tenth. As a result of

the rounding, some of the values in the HighC and LowC columns do not reflect the

range value output for the Range column. When you round numeric data values, this

type of error sometimes occurs. If you want to avoid this problem, then you can

specify additional decimal places in the format.

For more information, see “Using Column Aliases” on page 146.

Assigning Values Conditionally

Using a Simple CASE Expression

CASE expressions enable you to interpret and change some or all of the data values in a

column to make the data more useful or meaningful.

You can use conditional logic within a query by using a CASE expression to

conditionally assign a value. You can use a CASE expression anywhere that you can use

a column name.

The following table, which is used in the next example, describes the world climate

zones (rounded to the nearest degree) that exist between Location 1 and Location 2:

32 Chapter 2 • Retrieving Data from a Single Table

Table 2.1 World Climate Zones

Climate zone Location 1

Latitude

at

Location

1 Location 2

Latitude

at

Location

2

North Frigid North Pole 90 Arctic Circle 67

North

Temperate

Arctic Circle 67 Tropic of Cancer 23

Torrid Tropic of Cancer 23 Tropic of Capricorn -23

South

Temperate

Tropic of Capricorn -23 Antarctic Circle -67

South Frigid Antarctic Circle -67 South Pole -90

In this example, a CASE expression determines the climate zone for each city based on

the value in the Latitude column in the Sql.WorldCityCoords table. The query also

assigns an alias of ClimateZone to the value. You must close the CASE logic with the

END keyword.

libname sql 'SAS-library';

proc sql outobs=12;

title 'Climate Zones of World Cities';

select City, Country, Latitude,

case

when Latitude gt 67 then 'North Frigid'

when 67 ge Latitude ge 23 then 'North Temperate'

when 23 gt Latitude gt -23 then 'Torrid'

when -23 ge Latitude ge -67 then 'South Temperate'

else 'South Frigid'

end as ClimateZone

from sql.worldcitycoords

order by City;

Creating New Columns 33

Output 2.11 Using a Simple CASE Expression

Using the CASE-OPERAND Form

You can also construct a CASE expression by using the CASE-OPERAND form, as in

the following example. This example selects states and assigns them to a region based on

the value of the Continent column:

libname sql 'SAS-library';

proc sql outobs=12;

title 'Assigning Regions to Continents';

select Name, Continent,

case Continent

when 'North America' then 'Continental U.S.'

when 'Oceania' then 'Pacific Islands'

else 'None'

end as Region

from sql.unitedstates;

Note: When you use the CASE-OPERAND form of the CASE expression, the

conditions must all be equality tests. That is, they cannot use comparison operators

or other types of operators, as are used in “Using a Simple CASE Expression” on

page 32.

34 Chapter 2 • Retrieving Data from a Single Table

Output 2.12 Using a CASE Expression in the CASE-OPERAND Form

Replacing Missing Values

The COALESCE function enables you to replace missing values in a column with a new

value that you specify. For every row that the query processes, the COALESCE function

checks each of its arguments until it finds a nonmissing value, and then returns that

value. If all of the arguments are missing values, then the COALESCE function returns a

missing value. For example, the following query replaces missing values in the

LowPoint column in the Sql.Continents table with the words Not Available:

libname sql 'SAS-library';

proc sql;

title 'Continental Low Points';

select Name, coalesce(LowPoint, 'Not Available') as LowPoint

from sql.continents;

Creating New Columns 35

Output 2.13 Using the COALESCE Function to Replace Missing Values

The following CASE expression shows another way to perform the same replacement of

missing values. However, the COALESCE function requires fewer lines of code to

obtain the same results:

libname sql 'SAS-library';

proc sql;

title 'Continental Low Points';

select Name, case

when LowPoint is missing then 'Not Available'

else Lowpoint

end as LowPoint

from sql.continents;

Specifying Column Attributes

You can specify the following column attributes, which determine how SAS data is

displayed:

• FORMAT=

• INFORMAT=

• LABEL=

• LENGTH=

If you do not specify these attributes, then PROC SQL uses attributes that are already

saved in the table or, if no attributes are saved, then it uses the default attributes.

The following example assigns a label of State to the Name column and a format of

COMMA10. to the Area column:

libname sql 'SAS-library';

proc sql outobs=12;

36 Chapter 2 • Retrieving Data from a Single Table