REPORT Procedure

Example 5: Creating a Column for Each Value of a Variable

Features:

PROC REPORT statement options: SPLIT=

BREAK statement options: SKIP

COLUMN statement: stacking variables

COMPUTE statement arguments:
with a computed variable as report-item
AFTER
DEFINE statement options:
ACROSS
ANALYSIS
COMPUTED
SUM

LINE statement: pointer controls

Data set: GROCERY
Formats: $SCTRFMT

$MGRFMT

$DEPTFMT

Details

The report in this example does the following:
  • consolidates multiple observations into one row
  • contains a column for each value of Department that is selected for the report (the departments that sell perishable items)
  • contains a variable that is not in the input data set
  • uses customized column headings, some of which contain blank lines
  • double-spaces between detail rows
  • uses pointer controls to control the placement of text and variable values in a customized summary

Program

libname proclib
'SAS-library';
options fmtsearch=(proclib);
proc report data=grocery nowd
            headline
            headskip
            split='*';
   column sector manager department,sales perish;
   define sector / group format=$sctrfmt. 'Sector' '';
   define manager / group format=$mgrfmt. 'Manager* ';
   define department / across format=$deptfmt. '_Department_';
   define sales / analysis sum format=dollar11.2 ' ';
   define perish / computed format=dollar11.2
                'Perishable*Total';
   break after manager / skip;
   compute perish;
      perish=sum(_c3_, _c4_);
   endcomp;
   compute after;
      line @4 57*'-';
      line @4 '|   Combined sales for meat and dairy : '
           @46 _c3_ dollar11.2 '   |';
      line @4 '|   Combined sales for produce : '
           @46 _c4_ dollar11.2 '   |';
      line @4 '|' @60 '|';
      line @4 '|   Combined sales for all perishables: '
           @46 _c5_ dollar11.2 '   |';
      line @4 57*'-';
   endcomp;
   where sector contains 'n'
         and (department='p1' or department='p2');
   title 'Sales Figures for Perishables in 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 the column headings. HEADSKIP writes a blank line beneath the underlining that HEADLINE writes. SPLIT= defines the split character as an asterisk (*) because the default split character (/) is part of the name of a department.
proc report data=grocery nowd
            headline
            headskip
            split='*';
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. Each item generates a heading, but the heading for Sales is set to blank in its definition. Because Sales is an analysis variable, its values fill the cells that are created by these two variables.
   column sector manager department,sales perish;
   define sector / group format=$sctrfmt. 'Sector' '';
   define manager / group format=$mgrfmt. 'Manager* ';
Define the across variable. PROC REPORT creates a column and a column heading for each formatted value of the across variable Department. PROC REPORT orders the columns by these values. PROC REPORT also generates a column heading that spans all these columns. Quoted text in the DEFINE statement for Department customizes this heading. In traditional (monospace) SAS output, PROC REPORT expands the heading with underscores to fill all columns that are created by the across variable. 
   define department / across format=$deptfmt. '_Department_';
Define the analysis variable. Sales is an analysis variable that is used to calculate the sum statistic. In each case, the value of Sales is the sum of Sales for all observations in one department in one group. (In this case, the value represents a single observation.)
   define sales / analysis sum format=dollar11.2 ' ';
Define the computed variable. The COMPUTED option indicates that PROC REPORT must compute values for Perish. You compute the variable's values in a compute block that is associated with Perish.
   define perish / computed format=dollar11.2
                'Perishable*Total';
Produce a report summary. This BREAK statement creates a default summary after the last row for each value of Manager. The only option that is in use is SKIP, which writes a blank line. You can use this technique to double-space in many reports that contains a group or order variable.
   break after manager / skip;
Calculate values for the computed variable. This compute block computes the value of Perish from the values for the Meat/Dairy department and the Produce department. Because the variables Sales and Department collectively define these columns, there is no way to identify the values to PROC REPORT by name. Therefore, the assignment statement uses column numbers to unambiguously specify the values to use. Each time PROC REPORT needs a value for Perish, it sums the values in the third and fourth columns of that row of the report.
   compute perish;
      perish=sum(_c3_, _c4_);
   endcomp;
Produce a customized summary. This compute block creates a customized summary at the end of the report. The first LINE statement writes 57 hyphens (-) starting in column 4. Subsequent LINE statements write the quoted text in the specified columns and the values of the variables _C3_, _C4_, and _C5_ with the DOLLAR11.2 format.  Note that the pointer control (@) is designed for the Listing destination. It has no effect on ODS destinations other than traditional SAS monospace output.
   compute after;
      line @4 57*'-';
      line @4 '|   Combined sales for meat and dairy : '
           @46 _c3_ dollar11.2 '   |';
      line @4 '|   Combined sales for produce : '
           @46 _c4_ dollar11.2 '   |';
      line @4 '|' @60 '|';
      line @4 '|   Combined sales for all perishables: '
           @46 _c5_ dollar11.2 '   |';
      line @4 57*'-';
   endcomp;
   where sector contains 'n'
         and (department='p1' or department='p2');
Specify the title.
   title 'Sales Figures for Perishables in Northern Sectors';
run;

Output

Sales Figures for Perishables in Northern Sectors
Copyright © SAS Institute Inc. All rights reserved.