Section 8.1: How to Summarize Data in Table Format


PROC FREQ (Chapter 5) is one procedure within the SAS system which will produce 
summaries of discrete data in terms of counts.

PROC FREQ DATA=sashelp.class;
TABLE age / NOcum NOpercent;
RUN;

Age    Frequency

 11           2
 12           5
 13           3
 14           4
 15           4
 16           1

PROC TABULATE also produces the same results in a table, which you can modify the 
structure with many options.

PROC TABULATE DATA=sashelp.class NOseps;
CLASS age;
TABLE age, n*f=3.0 / rts=6;
RUN;
----------
|    | N |
|----+---|
|Age |   |
|11  |  2|
|12  |  5|
|13  |  3|
|14  |  4|
|15  |  4|
|16  |  1|
----------

While PROC FREQ is helpful for understanding the distribution of a few discrete 
variables in tabular form and will print many summary statistics for two-way tables, 
PROC TABULATE has features that help you summarize the contents of a dataset according 
to chosen classification factors with more control over the presentation format and its 
ability to cross-classify many variables. It can also compute summary statistics (e.g., 
counts, mean, min, max, and variance) for continuous data for each combination of the 
classification factor levels. With a unique combination of id variables it can also 
place the actual data values into tabular form. Here is another basic example:

OPTIONS NOcenter;
TITLE1 'Tables to show data and summary statistics';

DATA one;
DROP d1 d2;
INPUT row column d1 d2 @@;
repl=1; y=d1; OUTPUT;
repl=2; y=d2; OUTPUT;
CARDS;
1 1 3.28 3.09  1 2 3.52 3.48  1 3 2.88 2.80
2 1 2.46 2.44  2 2 1.87 1.92  2 3 2.19 2.19
3 1 2.77 2.66  3 2 3.74 3.44  3 3 2.55 2.55
4 1 3.78 3.87  4 2 4.07 4.12  4 3 3.31 3.31
;

PROC TABULATE DATA=one NOseps;
CLASS row column repl;
VAR y;
TABLE row*repl, column*y=' '*SUM=' '*f=5.2 / rts=11 BOX='Actual data';
RUN;

-----------------------------
|Actual   |     column      |
|data     |-----------------|
|         |  1  |  2  |  3  |
|---------+-----+-----+-----|
|row  repl|     |     |     |
|1    1   | 3.28| 3.52| 2.88|
|     2   | 3.09| 3.48| 2.80|
|2    1   | 2.46| 1.87| 2.19|
|     2   | 2.44| 1.92| 2.19|
|3    1   | 2.77| 3.74| 2.55|
|     2   | 2.66| 3.44| 2.55|
|4    1   | 3.78| 4.07| 3.31|
|     2   | 3.87| 4.12| 3.31|
-----------------------------

The role of each statement can be inferred from these examples; here is a brief 
description of the most important statements:

CLASS: indicates the categorical data that define
       classification factors for the rows and columns
  VAR: lists one or more numerical variables on which
       summary statistics are to be computed
TABLE: define the table structure and its contents

The most basic statements in the procedure include:

PROC TABULATE DATA=<dataset> NOseps;
CLASS <tab_vars> <row_vars> <col_vars>;
VAR y;
TABLE <tab_vars>, <row_vars>,
      <col_vars>*<numeric>*<stat>*<format>  / < options >;
RUN;

The NOseps option on the PROC TABULATE statement prints tables with only the borders and 
without the horizontal lines between each row of the table data.

Variables listed on the CLASS statement include:

 * <tab_vars> = tabl1*tabl2.. make a separate table for combinations of level
                              of these variables
 * <col_vars> = cvar1*cvar2.. levels are printed across the top of the table
 * <row_vars> = rvar1*rvar2.. levels are printed along
                              the left-hand side of the table

Separate tables are printed according to the combination of levels with the variables 
<tab_vars>. All possible combinations of levels are formed with the asterisk (*) 
separating the variables, for example rvar1*rvar2.

One of PROC TABULATE's most useful functions is to produce tables of specified summary 
statistics defined by multiple row and column level definitions. The following procedure 
prints the sample size, mean, and variance for each combination of the levels of 
classification factors in the table.

PROC TABULATE DATA=one NOseps;
CLASS row column repl;
VAR y;
TABLE row, column*y*(N*f=2.0 MEAN*f=6.2 VAR*f=9.6)
           / BOX='Summary Statistics' rts=14;
TABLE row, y*(N*f=2.0 MEAN*f=6.2 VAR*f=8.5)
           / BOX='Row Summary Statistics' rts=14;
RUN;

The following table of summary statistics is printed by the second TABLE statement:

---------------------------------- 
|Row Summary |         Y         |
|Statistics  |-------------------|
|            | N | MEAN |  VAR   |
|------------+---+------+--------|
|ROW         |   |      |        |
|1           |  6|  3.18| 0.09159|
|2           |  6|  2.18| 0.06198|
|3           |  6|  2.95| 0.26014|
|4           |  6|  3.74| 0.12831|
-----------------------------------

PROC TABULATE from Version 8 knows the option OUT=, so that you may write the results to 
a dataset which may then be exported to Excel.

To export a table into Excel and keep its tabular structure enter the ODS statements.


ODS HTML

PROC TABULATE;
etc.
RUN;

ODS HTML close;

You can export the table you see in the results viewer directly into an Excel worksheet 
which can then be formatted to your specifications. See Chapter 11 for a demonstration.