Programming with the SQL Procedure

Formatting PROC SQL Output by Using the REPORT Procedure

SQL provides limited output formatting capabilities. Some SQL vendors add output formatting statements to their products to address these limitations. SAS has reporting tools that enhance the appearance of PROC SQL output.

For example, SQL cannot display only the first occurrence of a repeating value in a column in its output. The following example lists cities in the USCITYCOORDS table. Notice the repeating values in the State column.

proc sql outobs=10;
   title 'US Cities';
   select State, City, latitude, Longitude
      from sql.uscitycoords
      order by state;

USCITYCOORDS Table Showing Repeating State Values

. . .

                                   US Cities

                 State  City                Latitude  Longitude
                 ----------------------------------------------
                 AK     Sitka                     57       -135
                 AK     Anchorage                 61       -150
                 AK     Nome                      64       -165
                 AK     Juneau                    58       -134
                 AL     Mobile                    31        -88
                 AL     Montgomery                32        -86
                 AL     Birmingham                33        -87
                 AR     Hot Springs               34        -93
                 AR     Little Rock               35        -92
                 AZ     Flagstaff                 35       -112

The following code uses PROC REPORT to format the output so that the state codes appear only once for each state group. A WHERE clause subsets the data so that the report lists the coordinates of cities in Pacific Rim states only. For more information about PROC REPORT, see the Base SAS Procedures Guide.

proc sql noprint;
   create table sql.cityreport as
   select * 
      from sql.uscitycoords
      group by state;

proc report data=sql.cityreport 
            headline 
            headskip;
   title 'Coordinates of U.S. Cities in Pacific Rim States';
   column state city ('Coordinates' latitude longitude);
   define state / order format=$2. width=5 'State';
   define city / order format=$15. width=15 'City';
   define latitude / display format=4. width=8 'Latitude';
   define longitude / display format=4. width=9 'Longitude';
   where state='AK' or 
         state='HI' or 
         state='WA' or 
         state='OR' or 
         state='CA';
run;

PROC REPORT Output Showing the First Occurrence Only of Each State Value

                Coordinates of U.S. Cities in Pacific Rim States

                                              Coordinates    
                  State  City             Latitude  Longitude
                  -------------------------------------------
                                                             
                  AK     Anchorage              61       -150
                         Juneau                 58       -134
                         Nome                   64       -165
                         Sitka                  57       -135
                  CA     El Centro              32       -115
                         Fresno                 37       -120
                         Long Beach             34       -118
                         Los Angeles            34       -118
                         Oakland                38       -122
                         Sacramento             38       -121
                         San Diego              33       -117
                         San Francisco          38       -122
                         San Jose               37       -122
                  HI     Honolulu               21       -158
                  OR     Baker                  45       -118
                         Eugene                 44       -124
                         Klamath Falls          42       -122
                         Portland               45       -123
                         Salem                  45       -123
                  WA     Olympia                47       -123
                         Seattle                47       -122
                         Spokane                48       -117

Top of Page