REPORT Procedure

Example 14: Using a Format to Create Groups

Features:	DEFINE statement options: GROUP
Other features:	FORMAT procedure
Data set:	GROCERY
Format:	$MGRFMT

Details

This example shows how to use formats to control the number of groups that PROC REPORT creates. The program creates a format for Department that classifies the four departments as one of two types: perishable or nonperishable. Consequently, when Department is an across variable, PROC REPORT creates only two columns instead of four. The column heading is the formatted value of the variable.

Program

libname proclib
'SAS-library';

options fmtsearch=(proclib);

proc format;
   value $perish 'p1','p2'='Perishable'
                'np1','np2'='Nonperishable';
run;

proc report data=grocery nowd
     headline
     headskip;

   column manager department,sales sales;

   define manager / group order=formatted
                    format=$mgrfmt.;
   define department / across order=formatted
                 format=$perish. '';

   define sales / analysis sum
                  format=dollar9.2 width=13;

   compute after;
      line ' ';
      line 'Total sales for these stores were: '
            sales.sum dollar9.2;
   endcomp;

title 'Sales Summary for All Stores';
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);

Create the $PERISH. format. PROC FORMAT creates a format for Department. This variable has four different values in the data set, but the format has only two values.

proc format;
   value $perish 'p1','p2'='Perishable'
                'np1','np2'='Nonperishable';
run;

Specify the report options. The NOWD option runs the REPORT procedure 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. Department and Sales are separated by a comma in the COLUMN statement, so they collectively determine the contents of the column that they define. Because Sales is an analysis variable, its values fill the cells that are created by these two variables. The report also contains a column for Manager and a column for Sales by itself (which is the sales for all departments).

   column manager department,sales sales;

Define the group and across variables. Manager is a group variable. Each detail row of the report consolidates the information for all observations with the same value of Manager. Department is an across variable. PROC REPORT creates a column and a column heading for each formatted value of Department. ORDER=FORMATTED arranges the values of Manager and Department alphabetically according to their formatted values. FORMAT= specifies the formats to use. The empty quotation marks in the definition of Department specify a blank column heading, so no heading spans all the departments. However, PROC REPORT uses the formatted values of Department to create a column heading for each individual department.

   define manager / group order=formatted
                    format=$mgrfmt.;
   define department / across order=formatted
                 format=$perish. '';

Define the analysis variable. Sales is an analysis variable that is used to calculate the Sum statistic. Sales appears twice in the COLUMN statement, and the same definition applies to both occurrences. FORMAT= specifies the format to use in the report. WIDTH= specifies the width of the column. Notice that the column headings for the columns that both Department and Sales create are a combination of the heading for Department and the (default) heading for Sales.

   define sales / analysis sum
                  format=dollar9.2 width=13;

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 ' ';
      line 'Total sales for these stores were: '
            sales.sum dollar9.2;
   endcomp;

Specify the title.

title 'Sales Summary for All Stores';
run;

Output