SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

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