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.

libname sql 'SAS-library';

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

USCITYCOORDS Table Showing Repeating State Values

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.

libname sql 'SAS-library';

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

proc report data=sql.cityreport 
            headline nowd
            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 US Cities in Pacific Rim Statues