REPORT Procedure

Example 4: Consolidating Multiple Observations into One Row of a Report

Features:

BREAK statement options:: OL; SKIP; SUMMARIZE; SUPPRESS

CALL DEFINE statement

Compute block: associated with a data set variable

COMPUTE statement arguments:: AFTER; a data set variable as report-item

DEFINE statement options:: ANALYSIS; GROUP; SUM; customizing column headings

LINE statement:: quoted text; variable values

Data set:

GROCERY

Formats:

$MGRFMT

$DEPTFMT

Details

This example creates a summary report that does the following:

consolidates information for each combination of Sector and Manager into one row of the report
contains default summaries of sales for each sector
contains a customized summary of sales for all sectors
uses one format for sales in detail rows and a different format in summary rows
uses customized column headings

Program

libname proclib
'SAS-library';

options fmtsearch=(proclib);

proc report data=grocery nowd headline headskip;

   column sector manager sales;

   define sector / group
                   format=$sctrfmt.
                   'Sector';
   define manager / group
                    format=$mgrfmt.
                    'Manager';
   define sales / analysis sum
                  format=comma10.2
                  'Sales';

   break after sector / ol
                        summarize
                        suppress
                        skip;

   compute after;
      line 'Combined sales for the northern sectors were '
            sales.sum dollar9.2 '.';
   endcomp;

   compute sales;
      if _break_ ne ' ' then
      call define(_col_,"format","dollar11.2");
   endcomp;

   where sector contains 'n';

   title 'Sales Figures for Northern Sectors';
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. 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 headline headskip;

Specify the report columns. The report contains columns for Sector, Manager, and Sales.

   column sector manager sales;

Define the group and analysis variables. In this report, Sector and Manager are group variables. Sales is an analysis variable that is used to calculate the Sum statistic. Each detail row represents a set of observations that have a unique combination of formatted values for all group variables. The value of Sales in each detail row is the sum of Sales for all observations in the group. FORMAT= specifies the format to use in the report. Text in quotation marks in a DEFINE statement specifies the column heading.

   define sector / group
                   format=$sctrfmt.
                   'Sector';
   define manager / group
                    format=$mgrfmt.
                    'Manager';
   define sales / analysis sum
                  format=comma10.2
                  'Sales';

Produce a report summary. This BREAK statement produces a default summary after the last row for each sector. OL writes a row of hyphens above the summary line. SUMMARIZE writes the value of Sales in the summary line. PROC REPORT sums the values of Sales for each manager because Sales is an analysis variable used to calculate the Sum statistic. SUPPRESS prevents PROC REPORT from displaying the value of Sector in the summary line. SKIP writes a blank line after the summary line.

   break after sector / ol
                        summarize
                        suppress
                        skip;

Produce a customized summary. This compute block creates a customized summary at the end of the report. The LINE statement writes the quoted text and the value of Sales.sum (with a format of DOLLAR9.2) in the summary. An ENDCOMP statement must end the compute block.

   compute after;
      line 'Combined sales for the northern sectors were '
            sales.sum dollar9.2 '.';
   endcomp;

Specify a format for the summary rows. In detail rows, PROC REPORT displays the value of Sales with the format that is specified in its definition (COMMA10.2). The compute block specifies an alternate format to use in the current column on summary rows. Summary rows are identified as a value other than a blank for _BREAK_.

   compute sales;
      if _break_ ne ' ' then
      call define(_col_,"format","dollar11.2");
   endcomp;

Select the observations to process. The WHERE statement selects for the report only the observations for stores in the northeast and northwest sectors. The TITLE statement specifies the title.

   where sector contains 'n';

Specify the title.

   title 'Sales Figures for Northern Sectors';
run;

Output