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

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

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