REPORT Procedure

Example 10: Calculating Percentages

Features:	COLUMN statement arguments: PCTSUM SUM spanning headings COMPUTE statement options: CHAR LENGTH= DEFINE statement options: COMPUTED FLOW WIDTH= RBREAK statement options: OL SUMMARIZE
Other features:	TITLE statement
Data set:	GROCERY
Formats:	$MGRFMT $DEPTFMT

Details

The summary report in this example shows the total sales for each store and the percentage that these sales represent of sales for all stores. Each of these columns has its own heading. A single heading also spans all the columns. This heading looks like a title, but it differs from a title because it would be stored in a report definition. You must submit a null TITLE statement whenever you use the report definition, or the report will contain both a title and the spanning heading.

The report includes a computed character variable, COMMENT, that flags stores with an unusually high percentage of sales. The text of COMMENT wraps across multiple rows. It makes sense to compute COMMENT only for individual stores. Therefore, the compute block that does the calculation includes conditional code that prevents PROC REPORT from calculating COMMENT on the summary line.

Program

libname proclib
'SAS-library';

options fmtsearch=(proclib);

proc report data=grocery nowd headline;
   title;

   column ('Individual Store Sales as a Percent of All Sales'
            sector manager sales,(sum pctsum) comment);

   define manager / group
                    format=$mgrfmt.;
   define sector / group
                   format=$sctrfmt.;
   define sales / format=dollar11.2
                  '';
   define sum / format=dollar9.2
                'Total Sales';

   define pctsum / 'Percent of Sales' format=percent6. width=8;
   define comment / computed width=20 '' flow;

   compute comment / char length=40;

      if sales.pctsum gt .15 and _break_ = ' '
      then comment='Sales substantially above expectations.';
      else comment=' ';
   endcomp;

   rbreak after / ol summarize;
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 format search library.The SAS system option FMTSEARCH= adds the SAS library PROCLIB to the search path that is used to locate formats.

proc report data=grocery nowd headline;
   title;

Specify the report columns. The COLUMN statement uses the text in quotation marks as a spanning heading. The heading spans all the columns in the report because they are all included in the pair of parentheses that contains the heading. The COLUMN statement associates two statistics with Sales: Sum and Pctsum. The Sum statistic sums the values of Sales for all observations that are included in a row of the report. The Pctsum statistic shows what percentage of Sales that sum is for all observations in the report.

   column ('Individual Store Sales as a Percent of All Sales'
            sector manager sales,(sum pctsum) comment);

Define the group and analysis columns. In this report, Sector and Manager are group variables. Each detail row represents a set of observations that have a unique combination of formatted values for all group variables. Sales is, by default, an analysis variable that is used to calculate the Sum statistic. However, because statistics are associated with Sales in the column statement, those statistics override the default. FORMAT= specifies the formats to use in the report. Text between quotation marks specifies the column heading.

   define manager / group
                    format=$mgrfmt.;
   define sector / group
                   format=$sctrfmt.;
   define sales / format=dollar11.2
                  '';
   define sum / format=dollar9.2
                'Total Sales';

Define the percentage and computed columns. The DEFINE statement for Pctsum specifies a column heading, a format, and a column width of 8. The PERCENT. format presents the value of Pctsum as a percentage rather than a decimal. The DEFINE statement for COMMENT defines it as a computed variable and assigns it a column width of 20 and a blank column heading. The FLOW option wraps the text for COMMENT onto multiple lines if it exceeds the column width.

   define pctsum / 'Percent of Sales' format=percent6. width=8;
   define comment / computed width=20 '' flow;

Calculate the computed variable. Options in the COMPUTE statement define COMMENT as a character variable with a length of 40.

   compute comment / char length=40;

Specify the conditional logic for the computed variable. For every store where sales exceeded 15% of the sales for all stores, this compute block creates a comment that says Sales substantially above expectations. Of course, on the summary row for the report, the value of Pctsum is 100. However, it is inappropriate to flag this row as having exceptional sales. The automatic variable _BREAK_ distinguishes detail rows from summary rows. In a detail row, the value of _BREAK_ is blank. The THEN statement executes only on detail rows where the value of Pctsum exceeds 0.15.

      if sales.pctsum gt .15 and _break_ = ' '
      then comment='Sales substantially above expectations.';
      else comment=' ';
   endcomp;

Produce the report summary. This RBREAK statement creates a default summary at the end of the report. OL writes a row of hyphens above the summary line. SUMMARIZE writes the values of Sales.sum and Sales.pctsum in the summary line.

   rbreak after / ol summarize;
run;

Output

Individual Store Sales as a Percent of All Sales