Example 2.5. Summarizing Columns and Rows
Goal
List observations in a data set, one observation per row, and summarize selected numeric variables. Summarize over all observations and summarize observations within groups. Summarize specific variables within an observation and add these summaries as columns in the report.
Report
|
Regional Energy
Quarterly Use by
Example Features
| Data Set | POWERUSE |
| Featured Step | PROC REPORT |
| Featured Step Statements and Options | DEFINE statement: ANALYSIS, COMPUTED, ORDER, and SUM options
COMPUTE blocks to create new variables BREAK AFTER statement: SUMMARIZE option RBREAK AFTER statement: SUMMARIZE option |
| Formatting Features | Placing a heading over the headings of several variables
Padding column headings with a character when sending output to the LISTING destination DEFINE statement: WIDTH option when sending output to the LISTING destination. Controlling line breaks in column headings BREAK and RBREAK statements: DOL, DUL, and SKIP options when sending output to the LISTING destination |
A Closer Look | Writing COMPUTE Blocks
Referencing Report Items in COMPUTE Blocks Processing Missing Values in COMPUTE Blocks Understanding How PROC REPORT Processes COMPUTE Blocks |
| Other Examples That Use This Data Set | Examples 2.4, 2.6, and 6.10 |
Example Overview
Example 2.4 presents monthly power usage by type of service for commercial and residential customers from January to June. This report includes the same information as Example 2.4 plus three new columns: two that summarize power usage for each of the two quarters represented in the report and one that summarizes power usage over the six-month reporting period. To produce these new summary columns, the program creates temporary report variables during execution of the report step.
Each detail row in the report—those with a value for SERVICE—corresponds to one observation in the POWERUSE data set. The other rows, which are summaries of the detail rows, were created by the PROC REPORT statements BREAK and RBREAK.
All the report columns except for the quarterly totals and overall total correspond to one variable in the POWERUSE data set. PROC REPORT defines the columns containing the totals. Statements in COMPUTE blocks in PROC REPORT calculate the totals by summing variables in the POWERUSE data set.
Program
| A Closer Look |
Writing COMPUTE Blocks
A COMPUTE block is a section of code that contains one or more SAS language programming statements. In the nonwindowing PROC REPORT environment, the COMPUTE and ENDCOMP statements enclose the code. In the windowing PROC REPORT environment, you enter the programming statements into the COMPUTE window.
A COMPUTE block can reference either a report item or a location. Report items include data set variables, statistics, and computed variables. The locations that a COMPUTE block can reference are the top of a report; the bottom of a report; before a group of observations; and after a group of observations. The second usage is usually associated with break lines and enables you to customize break lines.
You can have more than one COMPUTE block per PROC REPORT step, as shown in the previous example, but you cannot nest the COMPUTE blocks.
COMPUTE blocks can incorporate most features of the SAS language, including the following items.
Referencing Report Items in COMPUTE Blocks
The way you reference a report item in a COMPUTE block depends on how you’ve defined its usage. You can reference a report item in four ways:
| □ | by name |
| □ | by a compound name that identifies both the variable and the name of the statistic that you calculate with it. A compound name has this form
variable-name.statistic |
| □ | by an alias that you create in the COLUMN statement or in the DEFINITION window |
| □ | by column number, in the form
'_Cn_' where n is the number of the column (from left to right) in the report |
When you reference columns by number, remember to include in your count the columns that you define with NOPRINT and NOZERO, even though these columns do not appear in your report.
Table 2.5a shows how to use each type of reference in a COMPUTE block.
| If the variable that you reference is this type... | Then refer to it by... | For example... |
|---|---|---|
| Group | Name [*] | department |
| Order | Name [*] | department |
| Computed | Name [*] | department |
| Display | Name [*] | department |
| Display sharing a column with a statistic | A compound name [*] | sales.sum |
| Analysis | A compound name [*] | sales.mean |
| Any type sharing a column with an across variable | Column number [**] | ‘_c3_’ |
[*] If the variable has an alias, then you must reference it with the alias.
[**] Even if the variable has an alias, you must reference it by column number.
Processing Missing Values in COMPUTE Blocks
As in DATA step programming, when you reference in a COMPUTE block a variable that has a missing value, the result is a missing value. PROC REPORT displays the current missing value designation, which by default is a blank for character variables and a period for numeric variables. Where appropriate, consider using functions in COMPUTE blocks to prevent the generation of missing values when at least one of the arguments is nonmissing. The program above uses the SUM function for this reason.
Understanding How PROC REPORT Processes COMPUTE Blocks
You can associate a COMPUTE block with a report item or with a location. The way PROC REPORT processes a COMPUTE block depends on its usage.
When a COMPUTE block is associated with a report item, PROC REPORT executes the COMPUTE block on every row of the report when it comes to the column for that report item. The value of a computed variable in any row of a report is the last value assigned to that variable during that execution of the programming statements in the COMPUTE block. PROC REPORT assigns values to the columns in a row of a report from left to right. Consequently, you cannot base the calculation of a computed variable on any variable that appears to its right in the report.
When a COMPUTE block is associated with a location, PROC REPORT executes the COMPUTE block only at that location. Because PROC REPORT calculates statistics for groups before it actually constructs the rows of the report, statistics for sets of detail rows are available before or after the rows are displayed, as are values for any variables based on these statistics.
Table 2.5b describes the processing of both types of COMPUTE blocks in a PROC REPORT step.
Where to Go from Here
PROC REPORT reference, usage information, and additional examples. See, “The REPORT Procedure” in the “Procedures” section of Base SAS 9.1 Procedures Guide.