The TABULATE Procedure

Concepts

Statistics Available in PROC TABULATE
Use the following keywords to request statistics in the TABLE statement. If a variable name (class or analysis) and a statistic name are the same, enclose the statistic name in single quotes.

Descriptive statistic keywords

COLPCTN PCTSUM

COLPCTSUM RANGE

CSS REPPCTN

CV REPPCTSUM

MAX ROWPCTN

MEAN ROWPCTSUM

MIN STDDEV|STD

N STDERR

NMISS SUM

PAGEPCTN SUMWGT

PAGEPCTSUM USS

PCTN VAR

Quantile statistic keywords

MEDIAN|P50 Q3|P75

P1 P90

P5 P95

P10 P99

Q1|P25 QRANGE

Hypothesis testing keyword

PROBT T

To compute standard error (STD), you must use VARDEF=DF in the PROC statement. To compute weighted quantiles, you must use QMETHOD=OS in the PROC statement.

Explanations of the keywords, the formulas that are used to calculate them, and the data requirements are discussed in Keywords and Formulas .

Formatting Class Variables
Use the FORMAT statement to assign a format to a class variable for the duration of a PROC TABULATE step. When you assign a format to a class variable, PROC TABULATE uses the formatted values to create categories, and it uses the formatted values in headings.

User-defined formats are particularly useful for grouping values into fewer categories. For example, if you have a class variable, Age, with values ranging from 1 to 99, you could create a user-defined format that groups the ages so that your tables contain a manageable number of categories. The following PROC FORMAT step creates a format that condenses all possible values of age into six groups of values.

proc format;
   value agefmt  0-29='Under 30'
                30-39='30-39'
                40-49='40-49'
                50-59='50-59'
                60-69='60-69'
                other='70 or over';
run;

For information on creating user-defined formats, see The FORMAT Procedure .

By default, PROC TABULATE includes in a table only those formats for which the frequency count is not zero and for which values are not missing. To include missing values for all class variables in the output, use the MISSING option in the PROC TABULATE statement, and to include missing values for selected class variables, use the MISSING option in a CLASS statement. To include formats for which the frequency count is zero, use the PRELOADFMT option in a CLASS statement and the PRINTMISS option in the TABLE statement, or use the CLASSDATA= option in the PROC TABULATE statement.

Formatting Values in Tables
The formats for data in table cells serve two purposes. They determine how PROC TABULATE displays the values, and they determine the width of the columns. The default format for values in table cells is 12.2. You can modify the format for printing values in table cells by

changing the default format with the FORMAT= option in the PROC TABULATE statement
crossing elements in the TABLE statement with the F= format modifier.

PROC TABULATE determines the format to use for a particular cell based on the following order of precedence for formats:

If no other formats are specified, PROC TABULATE uses the default format (12.2).
The FORMAT= option in the PROC TABULATE statement changes the default format. If no format modifiers affect a cell, PROC TABULATE uses this format for the value in that cell.
A format modifier in the page dimension applies to the values in all the table cells on the page unless you specify another format modifier for a cell in the row or column dimension.
A format modifier in the row dimension applies to the values in all the table cells in the row unless you specify another format modifier for a cell in the column dimension.
A format modifier in the column dimension applies to the values in all the table cells in the column.

For more information about formatting table cells, see "Formatting Values in Table Cells" in Chapter 5, "Controlling the Table's Appearance" in SAS Guide to TABULATE Processing.

How Using BY-group Processing Differs from Using the Page Dimension
Using the page-dimension expression in a TABLE statement can have an effect similar to using a BY statement.

Contrasting the BY Statement and the Page Dimension contrasts the two methods.

Contrasting the BY Statement and the Page Dimension
Issue PROC TABULATE with a BY statement PROC TABULATE with a page dimension in the TABLE statement

Order of observations in the input data set The observations in the input data set must be sorted by the BY variables. (table note 1) Sorting is unnecessary.

One report summarizing all BY groups You cannot create one report for all the BY groups. Use ALL in the page dimension to create a report for all classes. (See Summarizing Information with the Universal Class Variable ALL .)

Percentages The percentages in the tables are percentages of the total for that BY group. You cannot calculate percentages for a BY group compared to the totals for all BY groups because PROC TABULATE prepares the individual reports separately. Data for the report for one BY group are not available to the report for another BY group. You can use denominator definitions to control the meaning of PCTN (see Calculating Percentages .)

Titles You can use the #BYVAL, #BYVAR, and #BYLINE specifications in TITLE statements to customize the titles for each BY group (see Creating Titles That Contain BY-Group Information ). The BOX= option in the TABLE statement customizes the page headers, but you must use the same title on each page.

Ordering class variables ORDER=DATA and ORDER=FREQ order each BY group independently. The order of class variables is the same on every page.

Obtaining uniform headings You may need to insert dummy observations into BY groups that do not have all classes represented. The PRINTMISS option ensures that each page of the table has uniform headings.

Multiple ranges with the same format PROC TABULATE produces a table for each range. PROC TABULATE combines observations from the two ranges.

***Contrasting the BY Statement and the Page Dimension***
Issue	PROC TABULATE with a BY statement	PROC TABULATE with a page dimension in the TABLE statement
Order of observations in the input data set	The observations in the input data set must be sorted by the BY variables. (table note 1)	Sorting is unnecessary.
One report summarizing all BY groups	You cannot create one report for all the BY groups.	Use ALL in the page dimension to create a report for all classes. (See Summarizing Information with the Universal Class Variable ALL .)
Percentages	The percentages in the tables are percentages of the total for that BY group. You cannot calculate percentages for a BY group compared to the totals for all BY groups because PROC TABULATE prepares the individual reports separately. Data for the report for one BY group are not available to the report for another BY group.	You can use denominator definitions to control the meaning of PCTN (see Calculating Percentages .)
Titles	You can use the #BYVAL, #BYVAR, and #BYLINE specifications in TITLE statements to customize the titles for each BY group (see Creating Titles That Contain BY-Group Information ).	The BOX= option in the TABLE statement customizes the page headers, but you must use the same title on each page.
Ordering class variables	ORDER=DATA and ORDER=FREQ order each BY group independently.	The order of class variables is the same on every page.
Obtaining uniform headings	You may need to insert dummy observations into BY groups that do not have all classes represented.	The PRINTMISS option ensures that each page of the table has uniform headings.
Multiple ranges with the same format	PROC TABULATE produces a table for each range.	PROC TABULATE combines observations from the two ranges.

TABLE NOTE 1: You can use the BY statement without sorting the data set if the data set has an index for the BY variable.

Calculating Percentages
The following statistics print the percentage of the value in a single table cell in relation to the total of the values in a group of cells. No denominator definitions are required; however, an analysis variable may be used as a denominator definition for percentage sum statistics.

REPPCTN and REPPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the report.

COLPCTN and COLPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the column.

ROWPCTN and ROWPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the row.

PAGEPCTN and PAGEPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the page.

These statistics calculate the most commonly used percentages. See Calculating Various Percentage Statistics for an example.

PCTN and PCTSUM statistics can be used to calculate these same percentages. They allow you to manually define denominators. PCTN and PCTSUM statistics print the percentage of the value in a single table cell in relation to the value (used in the denominator of the calculation of the percentage) in another table cell or to the total of the values in a group of cells. By default, PROC TABULATE summarizes the values in all N cells (for PCTN) or all SUM cells (for PCTSUM) and uses the summarized value for the denominator. You can control the value that PROC TABULATE uses for the denominator with a denominator definition.

You place a denominator definition in angle brackets (< and >) next to the N or PCTN statistic. The denominator definition specifies which categories to sum for the denominator.

This section illustrates how to specify denominator definitions in a simple table. Using Denominator Definitions to Display Basic Frequency Counts and Percentages illustrates how to specify denominator definitions in a table that is composed of multiple subtables. For more examples of denominator definitions, see "How Percentages Are Calculated" in Chapter 3, "Details of TABULATE Processing" in SAS Guide to TABULATE Processing.

Specifying a Denominator for the PCTN Statistic

The following PROC TABULATE step calculates the N statistic and three different versions of PCTN using the data set ENERGY .

proc tabulate data=energy;
   class division type;
   table division*
           (n='Number of customers'
            pctn<type>='% of row' [1] 
            pctn<division>='% of column'  [2]
            pctn='% of all customers'), [3]
          type/rts=50;
   title 'Number of Users in Each Division';
run;

The TABLE statement creates a row for each value of Division and a column for each value of Type. Within each row, the TABLE statement nests four statistics: N and three different calculations of PCTN (see Three Different Uses of the PCTN Statistic with Frequency Counts Highlighted ). Each occurrence of PCTN uses a different denominator definition.

Three Different Uses of the PCTN Statistic with Frequency Counts Highlighted

[IMAGE]

<type> sums the frequency counts for all occurrences of Type within the same value of Division. Thus, for Division=1, the denominator is 6 + 6, or 12.
<division> sums the frequency counts for all occurrences of Division within the same value of Type. Thus, for Type=1, the denominator is 6 + 3 + 8 + 5, or 22.
The third use of PCTN has no denominator definition. Omitting a denominator definition is the same as including all class variables in the denominator definition. Thus, for all cells, the denominator is 6 + 3 + 8 + 5 + 6 + 3 + 8 + 5, or 44.

Specifying a Denominator for the PCTSUM Statistic

The following PROC TABULATE step sums expenditures for each combination of Type and Division and calculates three different versions of PCTSUM.

proc tabulate data=energy format=8.2;
   class division type;
   var expenditures;
   table division*
           (sum='Expenditures'*f=dollar10.2
            pctsum<type>='% of row' [1] 
            pctsum<division>='% of column' [2]  
            pctsum='% of all customers'), [3]      
         type*expenditures/rts=40;
   title 'Expenditures in Each Division';
run;

The TABLE statement creates a row for each value of Division and a column for each value of Type. Because Type is crossed with Expenditures, the value in each cell is the sum of the values of Expenditures for all observations that contribute to the cell. Within each row, the TABLE statement nests four statistics: SUM and three different calculations of PCTSUM (see Three Different Uses of the PCTSUM Statistic with Sums Highlighted ). Each occurrence of PCTSUM uses a different denominator definition.

Three Different Uses of the PCTSUM Statistic with Sums Highlighted

[IMAGE]

<type> sums the values of Expenditures for all occurrences of Type within the same value of Division. Thus, for Division=1, the denominator is $7,477 + $5,129.
<division> sums the frequency counts for all occurrences of Division within the same value of Type. Thus, for Type=1, the denominator is $7,477 + $19,379 + $5,476 + $13,959.
The third use of PCTN has no denominator definition. Omitting a denominator definition is the same as including all class variables in the denominator definition. Thus, for all cells, the denominator is $7,477 + $19,379 + $5,476 + $13,959 + $5,129 + $15,078 + $4,729 + $12,619.

Using Style Elements in PROC TABULATE
If you use the Output Delivery System to create both HTML and Printer output from PROC TABULATE, you can set the style element that the procedure uses for various parts of the table. Style elements determine presentation attributes, such as font face, font weight, color, and so forth. Information about the style attributes that you can set for a style element is in Customizing the Style Definition That ODS Uses . Default Styles for Table Regions lists the default styles for various regions of a table.

Default Styles for Table Regions
Region Style

column headings Header

continuation message Aftercaption

box Header

page dimension text Beforecaption

row headings Rowheader

data cells Data

table Table

You specify style elements for PROC TABULATE with the STYLE= option. The following Using the STYLE= Option in PROC TABULATE shows where you can use this option. Specifications in the TABLE statement override the same specification in the PROC TABULATE statement. However, any style attributes that you specify in the PROC TABULATE statement and that you do not override in the TABLE statement are inherited. For instance, if you specify a blue background and a white foreground for all data cells in the PROC TABULATE statement, and you specify a gray background for the data cells of a particular crossing in the TABLE statement, the background for those data cells is gray, and the foreground is white (as specified in the PROC TABULATE statement).

***Default Styles for Table Regions***
Region	Style
column headings	Header
continuation message	Aftercaption
box	Header
page dimension text	Beforecaption
row headings	Rowheader
data cells	Data
table	Table

Detailed information on STYLE= is provided in the documentation for individual statements.

Using the STYLE= Option in PROC TABULATE
To set the style element for Use STYLE in this statement

data cells PROC TABULATE

page dimension text, continuation messages, and class variable name headings CLASS

class level value headings CLASSLEV

keyword headings KEYWORD

the entire table TABLE

analysis variable name headings VAR

***Using the STYLE= Option in PROC TABULATE***
To set the style element for	Use STYLE in this statement
data cells	PROC TABULATE
page dimension text, continuation messages, and class variable name headings	CLASS
class level value headings	CLASSLEV
keyword headings	KEYWORD
the entire table	TABLE
analysis variable name headings	VAR

Chapter Contents
Previous
Next
Top of Page

Descriptive statistic keywords
COLPCTN	PCTSUM
COLPCTSUM	RANGE
CSS	REPPCTN
CV	REPPCTSUM
MAX	ROWPCTN
MEAN	ROWPCTSUM
MIN	STDDEV\|STD
N	STDERR
NMISS	SUM
PAGEPCTN	SUMWGT
PAGEPCTSUM	USS
PCTN	VAR
Quantile statistic keywords
MEDIAN\|P50	Q3\|P75
P1	P90
P5	P95
P10	P99
Q1\|P25	QRANGE
Hypothesis testing keyword
PROBT	T

	REPPCTN and REPPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the report.
	COLPCTN and COLPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the column.
	ROWPCTN and ROWPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the row.
	PAGEPCTN and PAGEPCTSUM statistics--print the percentage of the value in a single table cell in relation to the total of the values in the page.