Producing Detail Reports with the PRINT Procedure

Creating Enhanced Reports

With just a few PROC PRINT statements and options, you can produce a variety of detail reports. By using additional statements and options that enhance the reports, you can do the following:

format the columns
sum the numeric variables
group the observations based on variable values
sum the groups of variable values
group the observations on separate pages

The examples in this section use the SAS data set QTR02, which was created in Input File and SAS Data Sets for Examples.

Specifying Formats for the Variables

Specifying the formats of variables is a simple yet effective way to enhance the readability of your reports. By adding the FORMAT statement to your program, you can specify formats for variables. The format of a variable is a pattern that SAS uses to write the values of the variables. For example, SAS contains formats that add commas to numeric values, that add dollar signs to figures, or that report values as Roman numerals.

Using a format can make the values of the variables Units and AmountSold easier to read than in the previous reports. Specifically, Units can use a COMMA format with a total field width of 7, which includes commas to separate every three digits and omits decimal values. AmountSold can use a DOLLAR format with a total field width of 14, which includes commas to separate every three digits, a decimal point, two decimal places, and a dollar sign.

The following program illustrates how to apply these formats in a FORMAT statement:

options linesize=80 pageno=1 nodate;

proc print data=qtr02 noobs;
   var SalesRep Month Units AmountSold;
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   title 'Quarterly Report for Sales above 500 Units or $20,000';
run;

PROC PRINT 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 Numeric Variables

             Quarterly Report for Sales above 500 Units or $20,000             1

              SalesRep         Month      Units        AmountSold

              Hollingsworth     04          530        $16,414.101 
              Jensen            04        1,1102       $34,376.70
              Garcia            04        1,715        $53,113.55
              Jensen            04          675        $20,904.75
              Hollingsworth     05        1,120        $34,686.40
              Hollingsworth     05        1,030        $31,899.10
              Garcia            06          512        $15,856.64
              Garcia            06        1,000        $30,970.00

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

	AmountSold uses the DOLLAR14.2 format. The maximum column width is 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.
	Units uses the COMMA7. format. The maximum column width is seven spaces. The column width includes the numeric value, commas, and a minus sign if a value is negative.

The formats do not affect the internal data values that are stored in the SAS data set. The formats change only how the current PROC step displays the values in the report.

Note: Be sure to specify enough columns in the format to contain the largest value. If the format that you specify is not wide enough to contain the largest value, including special characters such as commas and dollar signs, then SAS applies the most appropriate format. [cautionend]

Summing Numeric Variables

In addition to reporting the values in a data set, you can add the SUM statement to compute subtotals and totals for the numeric variables. The SUM statement enables you to request totals for one or more variables.

The following program produces a report that shows totals for the two numeric variables Units and AmountSold:

options linesize=80 pageno=1 nodate;

proc print data=qtr02 noobs;
   var SalesRep Month Units AmountSold;
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   sum Units AmountSold;
   title 'Quarterly Sales Total for Sales above 500 Units or $20,000';
run;

The following output shows the report:

Summing Numeric Variables

          Quarterly Sales Totals for Sales above 500 Units or $20,000          1

              SalesRep         Month      Units        AmountSold

              Hollingsworth     04          530        $16,414.10
              Jensen            04        1,110        $34,376.70
              Garcia            04        1,715        $53,113.55
              Jensen            04          675        $20,904.75
              Hollingsworth     05        1,120        $34,686.40
              Hollingsworth     05        1,030        $31,899.10
              Garcia            06          512        $15,856.64
              Garcia            06        1,000        $30,970.00
                                        =======    ==============
                                          7,692       $238,221.24

The totals for Units and AmountSold are computed by summing the values for each sale made by all the sales representatives. As the next example shows, the PRINT procedure can also separately compute subtotals for each sales representative.

Grouping Observations by Variable Values

The BY statement enables you to obtain separate analyses on groups of observations. The previous example used the SUM statement to compute totals for the variables Units and AmountSold. However, the totals were for all three sales representatives as one group. The next two examples show how to use the BY and ID statements as a part of the PROC PRINT step to separate the sales representatives into three groups with three separate subtotals and one grand total.

Computing Group Subtotals

To obtain separate subtotals for specific numeric variables, add a BY statement to the PROC PRINT step. When you use a BY statement, the PRINT procedure expects that you already sorted the data set by using the BY variables. Therefore, if your data is not sorted in the proper order, then you must add a PROC SORT step before the PROC PRINT step.

The BY statement produces a separate section of the report for each BY group. Do not specify in the VAR statement the variable that you use in the BY statement. Otherwise, the values of the BY variable appear twice in the report, as a header across the page and in columns down the page.

The following program uses the BY statement in the PROC PRINT step to obtain separate subtotals of the variables Units and AmountSold for each sales representative:

options linesize=80 pageno=1 nodate;

proc sort data=qtr02;
   by SalesRep;1 
run;

proc print data=qtr02 noobs;
   var Month Units AmountSold;2 
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   sum Units AmountSold;
   by SalesRep;2 
   title1 'Sales Rep Quarterly Totals for Sales Above 500 Units or $20,000'; 
  run;

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

	The BY statement in the PROC SORT step sorts the data.
	The variable SalesRep becomes part of the BY statement instead of the VAR statement.

The following output shows the report:

Grouping Observations with the BY Statement

        Sales Rep Quarterly Totals for Sales above 500 Units or $20,000        1

------------------------------- SalesRep=Garcia --------------------------------1 

                     Month         Units        AmountSold

                     04            1,715        $53,113.55
                     06              512        $15,856.64
                     06            1,000        $30,970.00
                     --------    -------    --------------
                     SalesRep      3,2272       $99,940.192 


---------------------------- SalesRep=Hollingsworth ----------------------------

                     Month         Units        AmountSold

                     04              530        $16,414.10
                     05            1,120        $34,686.40
                     05            1,030        $31,899.10
                     --------    -------    --------------
                     SalesRep      2,680        $82,999.60


------------------------------- SalesRep=Jensen --------------------------------

                     Month         Units        AmountSold

                     04            1,110        $34,376.70
                     04              675        $20,904.75
                     --------    -------    --------------
                     SalesRep      1,785        $55,281.45
                                 =======    ==============
                                   7,6923      $238,221.243

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

	The values of the BY variables appear in dashed lines, called BY lines, above the output for the BY group.
	The subtotal for the numeric variables is computed for each BY group (the three sales representatives).
	A grand total is computed for the numeric variables.

Identifying Group Subtotals

You can use both the BY and ID statements in the PROC PRINT step to modify the appearance of your report. When you specify the same variables in both the BY and ID statements, the PRINT procedure uses the ID variable to identify the start of the BY group.

The following example uses the data set that was sorted in the last example and adds the ID statement to the PROC PRINT step:

options linesize=80 pageno=1 nodate;

proc print data=qtr02;
   var Month Units AmountSold;
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   sum Units AmountSold;
   by SalesRep;
   id SalesRep;
   title1 'Sales Rep Quarterly Totals for Sales above 500 Units or $20,000'; 
   run;

The following output shows the report:

Grouping Observations with the BY and ID Statements

        Sales Rep Quarterly Totals for Sales above 500 Units or $20,000        1

              SalesRep         Month      Units        AmountSold

              Garcia            04        1,715        $53,113.55
                                06          512        $15,856.64
                                06        1,000        $30,970.00
              -------------             -------    --------------
              Garcia                      3,227        $99,940.19

              Hollingsworth     04          530        $16,414.10
                                05        1,120        $34,686.40
                                05        1,030        $31,899.10
              -------------             -------    --------------
              Hollingsworth               2,680        $82,999.60

              Jensen            04        1,110        $34,376.70
                                04          675        $20,904.75
              -------------             -------    --------------
              Jensen                      1,785        $55,281.45
                                        =======    ==============
                                          7,692       $238,221.24

The report has two distinct features. PROC PRINT separates the report into groups and suppresses the repetitive values of the BY and ID variables. The dashed lines above the BY groups do not appear because the BY and ID statements are used together in the PROC PRINT step.

Remember these general rules about the SUM, BY, and ID statements:

You can specify a variable in the SUM statement while omitting it in the VAR statement. PROC PRINT simply adds the variable to the list of variables in the VAR statement.
You do not specify variables in the SUM statement that you used in the ID or BY statement.
When you use a BY statement and you specify only one BY variable, PROC PRINT subtotals the SUM variable for each BY group that contains more than one observation.
When you use a BY statement and you specify multiple BY variables, PROC PRINT shows a subtotal for a BY variable only when the value changes and when there are multiple observations with that value.

Computing Multiple Group Subtotals

You can also use two or more variables in a BY statement to define groups and subgroups. The following program produces a report that groups observations first by sales representative and then by month:

options linesize=80 pageno=1 nodate;

proc sort data=qtr02;
   by SalesRep Month;1 
run;

proc print data=qtr02 noobs n='Sales Transactions:'2 
                              'Total Sales Transactions:'2 ;
   var Units AmountSold;3 
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   sum Units AmountSold;
   by SalesRep Month3 ;
   title1 'Monthly Sales Rep Totals for Sales above 500 Units or $20,000';
run;

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

	The BY statement in the PROC SORT step sorts the data by SalesRep and Month.
	The N= option in the PROC PRINT statement reports the number of observations in a BY group and (because of the SUM statement) the overall total number of observations at the end of the report. The first piece of explanatory text that N= provides precedes the number for each BY group. The second piece of explanatory text that N= provides precedes the number for the overall total.
	The variables SalesRep and Month are omitted in the VAR statement because the variables are specified in the BY statement. This prevents PROC PRINT from reporting the values for these variables twice.

The following output shows the report:

Grouping Observations with Multiple BY Variables

         Monthly Sales Rep Totals for Sales above 500 Units or $20,000         1

--------------------------- SalesRep=Garcia Month=04 ---------------------------

                             Units        AmountSold

                             1,715        $53,113.55

                              Sales Transactions:11 


--------------------------- SalesRep=Garcia Month=06 ---------------------------

                             Units        AmountSold

                               512        $15,856.64
                             1,000        $30,970.00
                           -------    --------------
                             1,5122       $46,826.642 
                             3,2273       $99,940.193 

                              Sales Transactions:2


----------------------- SalesRep=Hollingsworth Month=04 ------------------------

                             Units        AmountSold

                               530        $16,414.10

                              Sales Transactions:1


----------------------- SalesRep=Hollingsworth Month=05 ------------------------

                             Units        AmountSold

                             1,120        $34,686.40
                             1,030        $31,899.10
                           -------    --------------
                             2,150        $66,585.50
                             2,680        $82,999.60

                              Sales Transactions:2


--------------------------- SalesRep=Jensen Month=04 ---------------------------

                             Units        AmountSold

                             1,110        $34,376.70
                               675        $20,904.75
                           -------    --------------
                             1,785        $55,281.45
                             1,785        $55,281.45
                           =======    ==============
                             7,6924      $238,221.244 

                     Sales Transactions:21 
               Total Sales Transactions:85

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

	The number of observations in the BY group is computed. This corresponds to the number of sales transactions for a sales representative in the month.
	When the BY group contains two or more observations, then a subtotal is computed for each numeric variable.
	When the value of the first variable in the BY group changes, then an overall subtotal is computed for each numeric variable. The values of Units and AmountSold are summed for every month that Garcia had sales transactions because the sales representative changes in the next BY group.
	The grand total is computed for the numeric variables.
	The number of observations in the whole report is computed. This corresponds to the total number of sales transactions for every sales representative during the second quarter.

Computing Group Totals

When you use multiple BY variables as in the previous example, you can suppress the subtotals every time a change occurs for the value of the BY variables. Use the SUMBY statement to control which BY variable causes subtotals to appear.

You can specify only one SUMBY variable, and this variable must also be specified in the BY statement. PROC PRINT computes sums when a change occurs to the following values:

the value of the SUMBY variable
the value of any variable in the BY statement that is specified before the SUMBY variable

For example, consider the following statements:

by Quarter SalesRep Month;
sumby SalesRep;

SalesRep is the SUMBY variable. In the BY statement, Quarter comes before SalesRep while Month comes after SalesRep. Therefore, these statements cause PROC PRINT to compute totals when either Quarter or SalesRep changes value, but not when Month changes value.

The following program omits the monthly subtotals for each sales representative by designating SALESREP as the variable to sum by:

options linesize=80 pageno=1 nodate;

proc print data=qtr02;
   var Units AmountSold;
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   sum Units AmountSold;
   by SalesRep Month;
   id SalesRep Month;
   sumby SalesRep;
   title1 'Sales Rep Quarterly Totals for Sales above 500 Units or $20,000'; 
run;

This program assumes that QTR02 data has been previously sorted by the variables SalesRep and Month.

The following output shows the report:

Combining Subtotals for Groups of Observations

        Sales Rep Quarterly Totals for Sales above 500 Units or $20,000        1

              SalesRep         Month      Units        AmountSold

              Garcia            04        1,715        $53,113.55

              Garcia            06          512        $15,856.64
                                          1,000        $30,970.00
              -------------    -----    -------    --------------
              Garcia                      3,227        $99,940.19

              Hollingsworth     04          530        $16,414.10

              Hollingsworth     05        1,120        $34,686.40
                                          1,030        $31,899.10
              -------------    -----    -------    --------------
              Hollingsworth               2,680        $82,999.60

              Jensen            04        1,110        $34,376.70
                                            675        $20,904.75
              -------------    -----    -------    --------------
              Jensen                      1,785        $55,281.45
                                        =======    ==============
                                          7,692       $238,221.24

Grouping Observations on Separate Pages

You can also create a report with multiple sections that appear on separate pages by using the PAGEBY statement with the BY statement. The PAGEBY statement identifies a variable in the BY statement that causes the PRINT procedure to begin the report on a new page when a change occurs to the following values:

the value of the BY variable
the value of any BY variable that precedes it in the BY statement

The following program uses a PAGEBY statement with the BY statement to create a report with multiple sections:

options linesize=80 pageno=1 nodate;

proc print data=qtr02 noobs;
   var Units AmountSold;
   where Units>500 or AmountSold>20000;
   format Units comma7. AmountSold dollar14.2;
   sum Units AmountSold;
   by SalesRep Month;
   id SalesRep Month;
   sumby SalesRep;
   pageby SalesRep;
   title1 'Sales Rep Quarterly Totals for Sales above 500 Units or $20,000'; 
run;

This program assumes that QTR02 data has been previously sorted by the variables SalesRep and Month.

The following output shows the report:

Grouping Observations on Separate Pages

        Sales Rep Quarterly Totals for Sales above 500 Units or $20,000        1

              SalesRep         Month      Units        AmountSold

              Garcia            04        1,715        $53,113.55

              Garcia            06          512        $15,856.64
                                          1,000        $30,970.00
              -------------    -----    -------    --------------
              Garcia                      3,227        $99,940.19

        Sales Rep Quarterly Totals for Sales above 500 Units or $20,000        2

              SalesRep         Month      Units        AmountSold

              Hollingsworth     04          530        $16,414.10

              Hollingsworth     05        1,120        $34,686.40
                                          1,030        $31,899.10
              -------------    -----    -------    --------------
              Hollingsworth               2,680        $82,999.60

        Sales Rep Quarterly Totals for Sales above 500 Units or $20,000        3

              SalesRep         Month      Units        AmountSold

              Jensen            04        1,110        $34,376.70
                                            675        $20,904.75
              -------------    -----    -------    --------------
              Jensen                      1,785        $55,281.45
                                        =======    ==============
                                          7,692       $238,221.24

A page breaks occurs in the report when the value of the variable SalesRep changes from Garcia to Hollingsworth and from Hollingsworth to Jensen .

Top of Page