REPORT Procedure

Example 2: Ordering the Rows in a Report

Features:
PROC REPORT statement options:
COLWIDTH=
HEADLINE
HEADSKIP
SPACING=
BREAK statement options:
OL
SKIP
SUMMARIZE

COMPUTE statement arguments: AFTER

DEFINE statement options:
ANALYSIS
FORMAT=
ORDER
ORDER=
SUM

ENDCOMP statement

LINE statement:
with quoted text
with variable values
Data set: GROCERY
Formats: $MGRFMT

$DEPTFMT

Details

This example does the following:
  • arranges the rows alphabetically by the formatted values of Manager and the internal values of Department (so that sales for the two departments that sell nonperishable goods precede sales for the two departments that sell perishable goods)
  • controls the default column width and the spacing between columns
  • underlines the column headings and writes a blank line beneath the underlining
  • creates a default summary of Sales for each manager
  • creates a customized summary of Sales for the whole report

Program

libname proclib
'SAS-library';
options fmtsearch=(proclib);
proc report data=grocery nowd
            colwidth=10
            spacing=5
            headline headskip;
   column manager department sales;
   define manager / order order=formatted format=$mgrfmt.;
   define department / order order=internal format=$deptfmt.;
   define sales / analysis sum format=dollar7.2;
   break after manager / ol
                         summarize
                         skip;
   compute after;
      line 'Total sales for these stores were: '
           sales.sum dollar9.2;
   endcomp;
   where sector='se';
   title 'Sales for the Southeast Sector';
 run;

Program Description

Declare the PROCLIB library. The PROCLIB library is used to store user-created formats.
libname proclib
'SAS-library';
Specify the format search library.The SAS system option FMTSEARCH= adds the SAS library PROCLIB to the search path that is used to locate formats.
options fmtsearch=(proclib);
Specify the report options. The NOWD option runs PROC REPORT without the REPORT window and sends its output to the open output destinations. COLWIDTH=10 sets the default column width to 10 characters. SPACING= puts five blank characters between columns. HEADLINE underlines all column headings and the spaces between them at the top of each page of the report. HEADSKIP writes a blank line beneath the underlining that HEADLINE writes.
proc report data=grocery nowd
            colwidth=10
            spacing=5
            headline headskip;
Specify the report columns. The report contains a column for Manager, Department, and Sales.
   column manager department sales;
Define the sort order variables. The values of all variables with the ORDER option in the DEFINE statement determine the order of the rows in the report. In this report, PROC REPORT arranges the rows first by the value of Manager (because it is the first variable in the COLUMN statement) and then by the values of Department. ORDER= specifies the sort order for a variable. This report arranges the rows according to the formatted values of Manager and the internal values of Department (np1, np2, p1, and p2). FORMAT= specifies the formats to use in the report.
   define manager / order order=formatted format=$mgrfmt.;
   define department / order order=internal format=$deptfmt.;
Define the analysis variable. Sum calculates the sum statistic for all observations that are represented by the current row. In this report each row represents only one observation. Therefore, the Sum statistic is the same as the value of Sales for that observation in the input data set. Using Sales as an analysis variable in this report enables you to summarize the values for each group and at the end of the report.
   define sales / analysis sum format=dollar7.2;
Produce a report summary. This BREAK statement produces a default summary after the last row for each manager. OL writes a row of hyphens above the summary line. SUMMARIZE writes the value of Sales (the only analysis or computed variable) in the summary line. PROC REPORT sums the values of Sales for each manager because Sales is an analysis variable that is used to calculate the Sum statistic. SKIP writes a blank line after the summary line.
   break after manager / ol
                         summarize
                         skip;
Produce a customized summary. This COMPUTE statement begins a compute block that produces a customized summary at the end of the report. The LINE statement places the quoted text and the value of Sales.sum (with the DOLLAR9.2 format) in the summary. An ENDCOMP statement must end the compute block.
   compute after;
      line 'Total sales for these stores were: '
           sales.sum dollar9.2;
   endcomp;
Select the observations to process. The WHERE statement selects for the report only the observations for stores in the southeast sector.
   where sector='se';
Specify the title.
   title 'Sales for the Southeast Sector';
 run;

Output

Sales for the Southeast Sector