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

NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.OCEAN has 4 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.RIVER has 12 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.SEA has 13 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.02 seconds
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.WATERFALL has 4 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds
How It Works
This solution uses the INTO clause to store values in macro variables. The first SELECT
statement counts the unique variables and stores the result in macro variable N. The
second SELECT statement creates a range of macro variables, one for each unique
value, and stores each unique value in one of the macro variables. Note the use of the
%LEFT function, which trims leading blanks from the value of the N macro variable.
The MAKEDS macro uses all the macro variables that were created in the PROC SQL
step. The macro uses a %DO loop to execute a DATA step for each unique value, writing
rows that contain a given value of Type to a SAS data set of the same name. The Type
variable is dropped from the output data sets.
For more information about SAS macros, see SAS Macro Language: Reference.
Using PROC SQL Tables in Other SAS
Procedures
Problem
You want to show the average high temperatures in degrees Celsius for European
countries on a map.
Background Information
The Sql.WorldTemps table has average high and low temperatures for various cities
around the world.
proc sql outobs=10;
title 'WorldTemps';
208 Chapter 6 Practical Problem-Solving with PROC SQL
select City, Country,avghigh, avglow
from sql.worldtemps
;
Output 6.24 WorldTemps (Partial Output)
Solution
Use the following PROC SQL and PROC GMAP code to produce the map. You must
license SAS/GRAPH software to use PROC GMAP.
options fmtsearch=(sashelp.mapfmts);
proc sql;
create table extremetemps as
select country, round((mean(avgHigh)-32)/1.8) as High,
input(put(country,$glcsmn.), best.) as ID
from sql.worldtemps
where calculated id is not missing and country in
(select name from sql.countries where continent='Europe')
group by country;
quit;
proc gmap map=maps.europe data=extremetemps all;
id id;
block high / levels=3;
title 'Average High Temperatures for European Countries';
title2 'Degrees Celsius'
run;
quit;
Using PROC SQL Tables in Other SAS Procedures 209
Output 6.25 PROC GMAP Output
How It Works
The SAS system option FMTSEARCH= tells SAS to search in the Sashelp.Mapfmts
catalog for map-related formats. In the PROC SQL step, a temporary table is created
with Country, High, and ID columns. The calculation
round((mean(avgHigh)-32)/1.8) does the following:
1. For countries that are represented by more than one city, the mean of the cities'
average high temperatures is used for that country.
2. That value is converted from degrees Fahrenheit to degrees Celsius.
3. The result is rounded to the nearest degree.
The PUT function uses the $GLCSMN. format to convert the country name to a country
code. The INPUT function converts this country code, which is returned by the PUT
function as a character value, into a numeric value that can be understood by the GMAP
procedure. See SAS Functions and CALL Routines: Reference for details about the PUT
and INPUT functions.
The WHERE clause limits the output to European countries by checking the value of the
Country column against the list of European countries that is returned by the in-line
view. Also, rows with missing values of ID are eliminated. Missing ID values could be
produced if the $GLCSMN. format does not recognize the country name.
The GROUP BY clause is required so that the mean temperature can be calculated for
each country rather than for the entire table.
210 Chapter 6 Practical Problem-Solving with PROC SQL
The PROC GMAP step uses the ID variable to identify each country and places a block
representing the High value on each country on the map. The ALL option ensures that
countries (such as the United Kingdom in this example) that do not have High values are
also drawn on the map. In the BLOCK statement, the LEVELS= option specifies how
many response levels are used in the graph. For more information about the GMAP
procedure, see SAS/GRAPH: Reference.
Using PROC SQL Tables in Other SAS Procedures 211

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