Creating Detail and Summary Reports with the REPORT Procedure

Creating More Sophisticated Reports

Understanding Column Width and Spacing

You can modify the column spacing and the column width by specifying options in either the PROC REPORT statement or the DEFINE statement. To control the spacing between columns, you can use the SPACING= option in the following statements:

PROC REPORT statement to specify the default number of blank characters between all columns
DEFINE statement to override the default value and to specify the number of blank characters to the left of a particular column

By default, PROC REPORT inserts two blank spaces between the columns. To remove space between columns, specify SPACING=0. The maximum space that PROC REPORT allows between columns depends on the number of columns in the report. The sum of all column widths plus the blank characters to left of each column cannot exceed the line size.

To specify the column widths, you can use the following options:

the COLWIDTH= option in the PROC REPORT statement to specify the default number of characters for columns that contain computed variables or numeric data set variables
the WIDTH= option in the DEFINE statement to specify the width of the column that PROC REPORT uses to display a report item

By default, the column width is nine characters for numeric values. You can specify the column width as small as one character and as large as the line size. PROC REPORT sets the width of a column by first looking at the WIDTH= option in the DEFINE statement. If you omit WIDTH=, then PROC REPORT uses a column width large enough to accommodate the format for a report item. If you do not assign a format, then the column width is either the length of the character variable or the value of the COLWIDTH= option.

You can adjust the column layout by specifying how to align the formatted values of a report item and the column header with the column width. The following options in the DEFINE statement align the columns:

CENTER	centers the column values and column header.
LEFT	left-aligns the column values and column header
RIGHT	right-aligns the column values and column header.

Modifying the Column Width and Spacing

The following program modifies column spacing in a summary report that shows the total yearly sales for each sales representative:

options linesize=80 pageno=1 nodate;

proc report data=year_sales nowindows spacing=3;1 
   column SalesRep Units AmountSold;
   define SalesRep /group right;2 
   define Units / analysis sum width=5;3 
   define AmountSold/ analysis sum width=10;3 
   title1 'TruBlend Coffee Makers Sales Report';
   title2 'Total Yearly Sales';
run;

The following list corresponds to the numbered items in the preceding program:

	The SPACING= option in the PROC REPORT statement inserts three blank characters between all the columns.
	The RIGHT option in the DEFINE statement right-aligns the name of the sales representative and the column header in the column.
	The WIDTH= options in the DEFINE statements specify enough space to accommodate column headers on one line.

The following output shows the report:

Adjusting Column Width and Spacing

                      TruBlend Coffee Makers Sales Report                      1
                               Total Yearly Sales

                            SalesRep   Units   AmountSold
                              Garcia   15969    512070.78
                       Hollingsworth   10620     347246.1
                              Jensen   14400     461162.6

The column width for SalesRep is 14 characters wide, which is the length of the variable.

Customizing Column Headers

Understanding the Structure of Column Headers

By default, PROC REPORT does not insert a vertical space beneath column headers to visually separate the detail rows from the headers. To further improve the appearance of a report, you can underline the column headers, insert a blank line beneath column headers, and specify your own column headers. The HEADLINE and HEADSKIP options in the PROC REPORT statement enable you to underline the column headers and insert a blank line after the column headers, respectively.

By default, SAS uses the variable name or the variable label, if the data set variable was previously assigned a label, for the column header. To specify a different column header, place text between single or double quotation marks in the DEFINE statement for the report item.

By default, PROC REPORT produces line breaks in the column header based on the width of the column. When you use multiple sets of quotation marks in the label, each set defines a separate line of the header. If you include split characters in the label, then PROC REPORT breaks the header when it reaches the split character and continues the header on the next line. By default, the split character is the slash (/). Use the SPLIT= option in the PROC REPORT statement to specify an alternative split character.

Modifying the Column Headers

The following program creates a summary report with multiple-line column headers for the variables SalesRep, Units, and AmountSold:

options linesize=80 pageno=1 nodate;

proc report data=year_sales nowindows spacing=3 headskip;1 
   column SalesRep Units AmountSold;
   define SalesRep /group 'Sales/Representative';2 
   define Units / analysis sum 'Units Sold' width=5;2 
   define AmountSold/ analysis sum 'Amount' 'Sold';2 
   title1 'TruBlend Coffee Makers Sales Report';
   title2 'Total Yearly Sales';
run;

The following list corresponds to the numbered items in the preceding program:

	The HEADSKIP option inserts a blank line after the column headers.
	The text in quotation marks specifies the column headers.

The SPLIT= option in the PROC REPORT statement is omitted because the label for SalesRep uses the default split character and the label for AmountSold identifies where to split the label by using multiple sets of quotation marks.

The following output shows the report:

Modifying the Column Headers

                      TruBlend Coffee Makers Sales Report                      1
                               Total Yearly Sales

                       Sales            Units      Amount
                       Representative    Sold        Sold
                                                         
                       Garcia           15969   512070.78
                       Hollingsworth    10620    347246.1
                       Jensen           14400    461162.6

The label Units Sold is split between two lines because the column width for this report item is 5 characters wide.

Specifying Formats

Using SAS Formats

A simple and effective way to enhance the readability of your reports is to specify a format for the report items. To assign a format to a column, you can use the FORMAT statement or the FORMAT= option in the DEFINE statement. The FORMAT statement only works for data set variables. The FORMAT= option assigns a SAS format or a user-defined format to any report item.

PROC REPORT determines how to format a report item by searching for the format to use in these places and in this order:

the FORMAT= option in the DEFINE statement
the FORMAT statement
the data set

PROC REPORT uses the first format that it finds. If you have not assigned a format, then PROC REPORT uses the BEST9. format for numeric variables and the $w. format for character variables.

Applying Formats to Report Items

The following program illustrates how to apply formats to the columns of a summary report of total yearly sales for each sales representative:

options linesize=80 pageno=1 nodate;

proc report data=year_sales nowindows spacing=3 headskip;
   column SalesRep Units AmountSold;
   define SalesRep / group 'Sales/Representative';
   define Units / analysis sum 'Units Sold' format=comma7.;
   define AmountSold / analysis sum 'Amount' 'Sold' format=dollar14.2;
   title1 'TruBlend Coffee Makers Sales Report';
   title2 'Total Yearly Sales';
run;

PROC REPORT applies the COMMA7. format to the values of the variable Units and the DOLLAR14.2 format to the values of the variable AmountSold.

The following output shows the report:

Formatting the Numeric Columns

                      TruBlend Coffee Makers Sales Report                      1
                               Total Yearly Sales

                   Sales              Units           Amount
                   Representative      Sold             Sold
                                                            
                   Garcia            15,969      $512,070.781 
                   Hollingsworth     10,6202     $347,246.10
                   Jensen            14,400      $461,162.60

The following list corresponds to the numbered items in the preceding report:

	The variable AmountSold uses the DOLLAR14.2 format for a maximum column width of 14 spaces. Two spaces are reserved for the decimal part of a value. The remaining 12 spaces include the decimal point, whole numbers, the dollar sign, commas, and a minus sign if a value is negative.
	The variable Units uses the COMMA7. format for a maximum column width of seven spaces. The column width includes the numeric value, commas, and a minus sign if a value is negative.

These formats do not affect the actual data values that are stored in the SAS data set. That is, the formats only affect the way values appear in a report.

Using Variable Values as Column Headers

Creating the Column Headers

To create column headers from the values of the data set variables and produce cross-tabulations, you can use the ACROSS option in a DEFINE statement. When you define an ACROSS variable, PROC REPORT creates a column for each value of the ACROSS variable.

Columns created by an ACROSS variable contain statistics or computed values. If nothing is above or below an ACROSS variable, then PROC REPORT displays the number of observations in the input data set that belong to a cell of the report (N statistic). A cell is a single unit of a report, formed by the intersection of a row and a column.

The examples in this section show you how to display frequency counts (the N statistic) and statistics that are computed for ANALYSIS variables. For information about placing computed variables in the cells of the report, see the REPORT procedure in Base SAS Procedures Guide.

Creating Frequency Counts

The following program creates a report that tabulates the number of sales for each sales representative:

options linesize=84 pageno=1 nodate;

proc report data=year_sales nowindows colwidth=5 headline;1 
   column SalesRep Type N;2 
   define SalesRep / group 'Sales Representative'; 
   define Type / across 'Coffee Maker';3 
   define N / 'Total';
   title1 'TruBlend Coffee Makers Yearly Sales Report';
   title2 'Number of Sales';
 run;

The following list corresponds to the numbered items in the preceding program:

	The HEADLINE option in the PROC REPORT statement underlines all column headers and the spaces between them.
	The COLUMN statement specifies that the report contain two data set variables and a calculated statistic, N. The N statistic causes PROC REPORT to add a third column that displays the number of observations for each sales representative.
	The DEFINE statement specifies that Type is an ACROSS variable.

The following output shows the report:

Showing Frequency Counts

                     TruBlend Coffee Makers Yearly Sales Report                    1
                                  Number of Sales

                     Sales              Coffee Maker1            
                     Representative  Deluxe    Standard  Total2 
                     -----------------------------------------
                     Garcia              4        36        40
                     Hollingsworth       8        24        32
                     Jensen              4        34        38

The following list corresponds to the numbered items in the preceding report:

Type is an ACROSS variable with nothing above or below it. Therefore, the report shows how many observations the input data set contains for each sales representative and coffee maker type.

The column for N statistic is labeled Total and contains the total number of observations for each sales representative.

By default, PROC REPORT ordered the columns of the ACROSS variable according to its formatted values. You can use the ORDER= option in the DEFINE statement to alter the sort order for an ACROSS variable. See Changing the Default Order of the Rows for more information.

Sharing a Column with Multiple Analysis Variables

You can create sophisticated cross-tabulation by having the value of ANALYSIS variables appear in columns that the ACROSS variable creates. When an ACROSS variable shares columns with one or more ANALYSIS variables, PROC REPORT will stack the columns. For example, you can share the columns of the ACROSS variable Type with the ANALYSIS variable Units so that each column contains the number of units sold for a type of coffee maker.

To stack the value of an ANALYSIS variable in the columns created by the ACROSS variable, place that variable next to the ACROSS variable in the COLUMN statement:

column SalesRep Type, Unit;

The comma separates the ACROSS variable from the ANALYSIS variable. To specify multiple ANALYSIS variables, list their names in parentheses next to the ACROSS variable in the COLUMN statement:

column SalesRep Type,(Unit AmountSold);

If you place the ACROSS variable before the ANALYSIS variable, then the name and values of the ACROSS variable are above the name of the ANALYSIS variable in the report. If you place the ACROSS variable after the ANALYSIS variable, then the name and the values of the ACROSS variable are below the name of the ANALYSIS variable.

By default, PROC REPORT calculates the SUM statistic for the ANALYSIS variables. To display another statistic for the column, use the DEFINE statement to specify the statistic that you want computed for the ANALYSIS variable. See the list Descriptive Statistics for a list of the available statistics.

The following program creates a report that tabulates the number of coffee makers sold and the average sale in dollars for each sales representative:

options linesize=84 pageno=1 nodate;

proc report data=year_sales nowindows headline;
   column SalesRep Type,(Units Amountsold);1  
   define SalesRep / group 'Sales Representative'; 
   define Type / across '';2 
   define units / analysis sum 'Units Sold' format=comma7.;3 
   define AmountSold /analysis mean 'Average/Sale' format=dollar12.2;4 
   title1 'TruBlend Coffee Makers Yearly Sales Report';
run;

The following list corresponds to the numbered items in the preceding program:

	The COLUMN statement creates columns for SalesRep and Type. The ACROSS variable Type shares its columns with the ANALYSIS variables Units and Amountsold.
	The DEFINE statement uses a blank as the label of Type in the column header.
	The DEFINE statement uses the ANALYSIS variable Units to compute a SUM statistic.
	The DEFINE statement uses the ANALYSIS variable AmountSold to compute a MEAN statistic.

The following output shows the report:

Sharing a Column with Multiple Analysis Variables

                     TruBlend Coffee Makers Yearly Sales Report                    1

                                                                        
                                   Deluxe                Standard       
            Sales             Units       Average    Units       Average
            Representative     Sold          Sale     Sold          Sale
            ------------------------------------------------------------
            Garcia              945    $11,694.38   15,024    $12,924.81
            Hollingsworth       760     $4,702.50    9,860    $12,901.09
            Jensen              820    $10,147.50   13,580    $12,369.78

The values in the columns for a particular type of coffee maker are the total units sold and the average dollar sale for each sales representative.

Summarizing Groups of Observations

Using Group Summaries

For some reports, you may want to summarize information about a group of observations and visually separate each group. To do so, you can create a break in the report before or after each group.

To visually separate each group, you insert lines of text, called break lines, at a break. Break lines can occur at the beginning or end of a report, at the top or bottom of each page, and whenever the value of a group or order variable changes. The break line can contain the following items:

text (including blanks)
summaries of statistics
report variables
computed variables

To create group summaries, use the BREAK statement. A BREAK statement must include (in this order) the following:

the keyword BREAK
the location of the break (BEFORE or AFTER)
the name of a GROUP variable that is called the break variable

PROC REPORT creates a break each time the value of the break variable changes. If you want summaries to appear before the first row of each group, then use the BEFORE argument. If you want the summaries to appear after the last row of each group, then use the AFTER argument.

To create summary information for the whole report, use the RBREAK statement. An RBREAK statement must include (in this order) the following:

the keyword RBREAK
the location of the break (BEFORE or AFTER)

When you use the RBREAK statement, PROC REPORT inserts text, summary statistics for the entire report, or computed variables at the beginning or end of the detail rows of a report. If you want the summary to appear before the first row of the report, then use the BEFORE argument. If you want the summaries to appear after the last row of each group, then use the AFTER argument.

Both the BREAK and RBREAK statements support options that control the appearance of the group and the report summaries. You can use any combination of options in the statement in any order. For a list of the available options, see the REPORT procedure in Base SAS Procedures Guide.

Creating Group Summaries

The following program creates a summary report that uses break lines to display subtotals with yearly sales for each sales representative, and a yearly grand total for all sales representatives:

options linesize=80 pageno=1 nodate linesize=84;

proc report data=year_sales nowindows headskip;
   column Salesrep Quarter Units AmountSold;
   define SalesRep / group 'Sales Representative';
   define Quarter / group center;1 
   define Units / analysis sum 'Units Sold' format=comma7.;
   define AmountSold / analysis sum 'Amount/Sold' format=dollar14.2;
   break after SalesRep / summarize skip ol suppress;2 
   rbreak after / summarize skip dol;3 
   title1 'TruBlend Coffee Makers Sales Report';
   title2 'Total Yearly Sales';
run;

The following list corresponds to the numbered items in the preceding program:

	The CENTER option in the DEFINE statement centers the values of the variable Quarter and the label of the column header.
	The BREAK statement adds break lines after a change in the value of the GROUP variable SalesRep. The SUMMARIZE option writes a summary line to summarize the statistics for each group of break lines. The SKIP option inserts a blank line after each group of break lines. The OL option writes a line of hyphens (-) above each value in the summary line. The SUPPRESS option suppresses printing the value of the break variable and the overlines in the break variable column.
	The RBREAK statement adds a break line at the end of the report. The SUMMARIZE option writes a summary line that summarizes the SUM statistics for the ANALYSIS variables Units and AmountSold. The SKIP option inserts a blank line before the break line. The DOL option writes a line of equal signs (=) above each value in the summary line.

The following output shows the report:

Creating Group Summaries

                        TruBlend Coffee Makers Sales Report                        1
                                 Total Yearly Sales

                 Sales                       Units          Amount
                 Representative  Quarter      Sold            Sold
                                                                  
                 Garcia             1        3,377     $118,019.94
                                    2        3,515     $108,859.55
                                    3        7,144     $225,326.28
                                    4        1,933      $59,865.01
                                           -------  --------------
                                            15,9691     $512,070.781 
                                                                  
                 Hollingsworth      1        1,770      $59,634.70
                                    2        3,090      $96,160.55
                                    3        3,285     $109,704.35
                                    4        2,475      $81,746.50
                                           -------  --------------
                                            10,620     $347,246.10
                                                                  
                 Jensen             1        1,617      $50,078.49
                                    2        2,413      $74,730.61
                                    3        6,687     $222,290.99
                                    4        3,683     $114,062.51
                                           -------  --------------
                                            14,400     $461,162.60
                                                                  
                                           =======  ==============
                                            40,9892   $1,320,479.482

The following list corresponds to the numbered items in the preceding report:

	The values of the ANALYSIS variables Units and AmountSold in the group summary lines are sums for all rows in the group (subtotals).
	The values of the ANALYSIS variables Units and AmountSold in the report summary line are sums for all rows in the report (grand totals).

In this report, Units and AmountSold are ANALYSIS variables that are used to calculate the SUM statistic. If these variables were defined to calculate a different statistic, then the values in the summary lines would be the value of that statistic for all rows in the group and all rows in the report.

Top of Page