Creating Summary Tables with the TABULATE Procedure

Creating Simple Summary Tables

Creating a Basic One-Dimensional Summary Table

The simplest summary table contains multiple columns but only a single row. It is called a one-dimensional summary table because it has only a column dimension. The PROC TABULATE step that follows creates a one-dimensional summary table that answers the question, "How many times did each sales representative make a sale?"

options linesize=84 pageno=1 nodate;

proc tabulate data=year_sales format=comma10.;
   title1 'TruBlend Coffee Makers, Inc.';
   title2 'Number of Sales by Each Sales Representative';
   class SalesRep;1 
   table SalesRep;2 
run;

The numbered items in the previous program correspond to the following:

	The variable SalesRep is specified as a class variable in the CLASS statement. A category will be created for each value of SalesRep wherever SalesRep is used in a TABLE statement.
	The variable SalesRep is specified in the column dimension of the TABLE statement. A column will be created for each category of SalesRep. Each column will show the number of times (N) that values belonging to the category appear in the data set.

The following summary table displays the results of this program:

Basic One-Dimensional Summary Table

                            TruBlend Coffee Makers, Inc.                           1
                    Number of Sales by Each Sales Representative

                         ----------------------------------
                         |            SalesRep            |
                         |--------------------------------|
                         |          |Hollingsw-|          |
                         |  Garcia  |   orth   |  Jensen  |
                         |----------+----------+----------|
                         |    N     |    N     |    N     |
                         |----------+----------+----------|
                         |        40|        32|        38|
                         ----------------------------------

The values 40, 32, and 38 are the frequency with which each sales representative's name (Garcia, Hollingsworth, and Jensen) occurs in the data set. For this data set, each occurrence of the sales representative's name in the data set represents a sale.

Creating a Basic Two-Dimensional Summary Table

The most commonly used form of a summary table has at least one column and multiple rows, and is called a two-dimensional summary table. The PROC TABULATE step that follows creates a two-dimensional summary table that answers the question, "What was the amount that was sold by each sales representative?"

options linesize=84 pageno=1 nodate;

proc tabulate data=year_sales format=comma10.;
   title1 'TruBlend Coffee Makers, Inc.';
   title2 'Amount Sold by Each Sales Representative';
   class SalesRep;1 
   var AmountSold;2 
   table SalesRep,3 
         AmountSold;4 ;
run;

The numbered items in the previous program correspond to the following:

	The variable SalesRep is specified as a class variable in the CLASS statement. A category will be created for each value of SalesRep wherever SalesRep is used in a TABLE statement.
	The variable AmountSold is specified as an analysis variable in the VAR statement. The values of AmountSold will be used to compute statistics wherever AmountSold is used in a TABLE statement.
	The variable SalesRep is in the row dimension of the TABLE statement. A row will be created for each value or category of SalesRep.
	The variable AmountSold is in the column dimension of the TABLE statement. The default statistic for analysis variables, SUM, will be used to summarize the values of AmountSold.

The following summary table displays the results of this program:

Basic Two-Dimensional Summary Table

                            TruBlend Coffee Makers, Inc.                           1
                      Amount Sold by Each Sales Representative

                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |2 
                          |-------------------+----------|
                          |SalesRep           |1          |
                          |-------------------|          |
                          |Garcia             |   512,071|
                          |-------------------+----------|
                          |Hollingsworth      |   347,246|
                          |-------------------+----------|
                          |Jensen             |   461,163|
                          --------------------------------

The numbered items in the previous SAS output correspond to the following:

	The variable AmountSold has been crossed with the variable SalesRep to produce each data cell of the summary table.
	The column heading AmountSold includes the subheading SUM. The values that are displayed in the column dimension are sums of the amount sold by each sales representative.

Creating a Basic Three-Dimensional Summary Table

Three-dimensional summary tables produce the output on separate pages with rows and columns on each page. The PROC TABULATE step that follows creates a three-dimensional summary table that answers the question, "What was the amount that was sold during each quarter of the year by each sales representative?"

options linesize=84 pageno=1 nodate;

proc tabulate data=year_sales format=comma10.;
   title1 'TruBlend Coffee Makers, Inc.';
   title2 'Quarterly Sales by Each Sales Representative';
   class SalesRep Quarter;1 
   var AmountSold;2 
   table SalesRep,3 
         Quarter,4 
         AmountSold;5 
run;

The numbered items in the previous program correspond to the following:

	The variables SalesRep and Quarter are specified as class variables in the CLASS statement. A category will be created for each value of SalesRep wherever SalesRep is used in the TABLE statement. Similarly, a category will be created for each value of Quarter wherever Quarter is used in a TABLE statement.
	The variable AmountSold is specified as an analysis variable in the VAR statement. The values of AmountSold will be used to compute statistics wherever AmountSold is used in a TABLE statement.
	The variable SalesRep is used in the page dimension of the TABLE statement. A page will be created for each value or category of SalesRep.
	The variable Quarter is used in the row dimension of the TABLE statement. A row will be created for each value or category of Quarter.
	The variable AmountSold is used in the column dimension of the TABLE statement. The default statistic for analysis variables, SUM, will be used to summarize the values of AmountSold.

The following summary table displays the results of this program:

Basic Three-Dimensional Summary Table

                            TruBlend Coffee Makers, Inc.                           1
                    Quarterly Sales by Each Sales Representative

                          SalesRep Garcia 1                
                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |3 
                          |-------------------+----------|
                          |Quarter            |2          |
                          |-------------------|          |
                          |1                  |   118,020|
                          |-------------------+----------|
                          |2                  |   108,860|
                          |-------------------+----------|
                          |3                  |   225,326|
                          |-------------------+----------|
                          |4                  |    59,865|
                          --------------------------------

                            TruBlend Coffee Makers, Inc.                           2
                    Quarterly Sales by Each Sales Representative

                          SalesRep Hollingsworth 1         
                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |3 
                          |-------------------+----------|
                          |Quarter            |2          |
                          |-------------------|          |
                          |1                  |    59,635|
                          |-------------------+----------|
                          |2                  |    96,161|
                          |-------------------+----------|
                          |3                  |   109,704|
                          |-------------------+----------|
                          |4                  |    81,747|
                          --------------------------------

                            TruBlend Coffee Makers, Inc.                           3
                    Quarterly Sales by Each Sales Representative

                          SalesRep Jensen 1                
                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |3 
                          |-------------------+----------|
                          |Quarter            |2          |
                          |-------------------|          |
                          |1                  |    50,078|
                          |-------------------+----------|
                          |2                  |    74,731|
                          |-------------------+----------|
                          |3                  |   222,291|
                          |-------------------+----------|
                          |4                  |   114,063|
                          --------------------------------

The numbered items in the previous SAS output correspond to the following:

	This summary table has a separate page for each sales representative.
	For each sales representative, the amount sold is reported for each quarter.
	The column heading AmountSold includes the subheading SUM. The values that are displayed in this column indicate the total amount sold in US dollars for each quarter by each sales representative.

Producing Multiple Tables in a Single PROC TABULATE Step

You can produce multiple tables in a single PROC TABULATE step. However, you cannot change the way a variable is used or defined in the middle of the step. In other words, the variables in the CLASS or VAR statements are defined only once for all TABLE statements in the PROC TABULATE step. If you need to change the way a variable is used or defined for different TABLE statements, then you must place the TABLE statements, and define the variables, in multiple PROC TABULATE steps. The program that follows produces three summary tables during one execution of the TABULATE procedure:

options linesize=84 pageno=1 nodate;

proc tabulate data=year_sales format=comma10.;
   title1 'TruBlend Coffee Makers, Inc.';
   title2 'Sales of Deluxe Model Versus Standard Model';
   class SalesRep Type;
   var AmountSold Units;
   table Type;1 
   table Type, Units;2 
   table SalesRep, Type, AmountSold;3 
run;

The numbered items in the previous program correspond to the following:

	The first TABLE statement produces a one-dimensional summary table with the values for the variable Type in the column dimension.
	The second TABLE statement produces a two-dimensional summary table with the values for the variable Type in the row dimension and the variable Units in the column dimension.
	The third TABLE statement produces a three-dimensional summary table with the values for the variable SalesRep in the page dimension, the values for the variable Type in the row dimension, and the variable AmountSold in the column dimension.

The following summary table displays the results of this program:

Multiple Tables Produced by a Single PROC TABULATE Step

                            TruBlend Coffee Makers, Inc.                           1
                    Sales of Deluxe Model Versus Standard Model

                              -----------------------
                              |        Type         |
                              |---------------------|
                              |  Deluxe  | Standard |
                              |----------+----------|
                              |    N     |    N     |
                              |----------+----------|
                              |        16|        94|
                              -----------------------

                            TruBlend Coffee Makers, Inc.                           2
                    Sales of Deluxe Model Versus Standard Model

                          --------------------------------
                          |                   |  Units   |
                          |                   |----------|
                          |                   |   Sum    |
                          |-------------------+----------|
                          |Type               |          |
                          |-------------------|          |
                          |Deluxe             |     2,525|
                          |-------------------+----------|
                          |Standard           |    38,464|
                          --------------------------------

                            TruBlend Coffee Makers, Inc.                           3
                    Sales of Deluxe Model Versus Standard Model

                          SalesRep Garcia                 
                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |
                          |-------------------+----------|
                          |Type               |          |
                          |-------------------|          |
                          |Deluxe             |    46,778|
                          |-------------------+----------|
                          |Standard           |   465,293|
                          --------------------------------

                            TruBlend Coffee Makers, Inc.                           4
                    Sales of Deluxe Model Versus Standard Model

                          SalesRep Hollingsworth          
                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |
                          |-------------------+----------|
                          |Type               |          |
                          |-------------------|          |
                          |Deluxe             |    37,620|
                          |-------------------+----------|
                          |Standard           |   309,626|
                          --------------------------------

                            TruBlend Coffee Makers, Inc.                           5
                    Sales of Deluxe Model Versus Standard Model

                          SalesRep Jensen                 
                          --------------------------------
                          |                   |AmountSold|
                          |                   |----------|
                          |                   |   Sum    |
                          |-------------------+----------|
                          |Type               |          |
                          |-------------------|          |
                          |Deluxe             |    40,590|
                          |-------------------+----------|
                          |Standard           |   420,573|
                          --------------------------------

Top of Page