Practical Problem-Solving with PROC SQL

Using PROC SQL Tables in Other SAS Procedures

You want to show the average high temperatures in degrees Celsius for European countries on a map.

The SQL.WORLDTEMPS table has average high and low temperatures for various cities around the world.

                                     WORLDTEMPS

              City                Country           AvgHigh    AvgLow
              -------------------------------------------------------
              Algiers             Algeria                90        45
              Amsterdam           Netherlands            70        33
              Athens              Greece                 89        41
              Auckland            New Zealand            75        44
              Bangkok             Thailand               95        69
              Beijing             China                  86        17
              Belgrade            Yugoslavia             80        29
              Berlin              Germany                75        25
              Bogota              Colombia               69        43
              Bombay              India                  90        68

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;

PROC GMAP Output

[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:

For countries that are represented by more than one city, the mean of the cities' average high temperatures is used for that country.
That value is converted from degrees Fahrenheit to degrees Celsius.
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 Language Reference: Dictionary 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.

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.

Top of Page