The TABULATE Procedure

PROC TABULATE Statement

PROC TABULATE <option(s)>;

To do this Use this option

Customize the HTML contents link to the output CONTENTS=

Specify the input data set DATA=

Disable floating point exception recovery NOTRAP

Specify the output data set OUT=

Enable floating point exception recovery TRAP

Identify categories of data that are of interest

Specify a secondary data set that contains the combinations of values of class variables to include in tables and output data sets CLASSDATA=

Exclude from tables and output data sets all combinations of class variable values that are not in the CLASSDATA= data set EXCLUSIVE

Consider missing values as valid values for class variables MISSING

Control the statistical analysis

Exclude observations with nonpositive weights EXCLNPWGTS

Specify the sample size to use for the P2 quantile estimation method QMARKERS=

Specify the quantile estimation method QMETHOD=

Specify the mathematical definition to calculate quantiles QNTLDEF=

Specify the variance divisor VARDEF=

Customize the appearance of the table

Specify a default format for each cell in the table FORMAT=

Define the characters to use to construct the table outlines and dividers FORMCHAR=

Eliminate horizontal separator lines from the row titles and the body of the table NOSEPS

Order the values of a class variable according to the specified order ORDER=

Specify the default style element or style elements (for the Output Delivery System) to use for each cell of the table STYLE=

To do this	Use this option
Customize the HTML contents link to the output	CONTENTS=
Specify the input data set	DATA=
Disable floating point exception recovery	NOTRAP
Specify the output data set	OUT=
Enable floating point exception recovery	TRAP
Identify categories of data that are of interest
	Specify a secondary data set that contains the combinations of values of class variables to include in tables and output data sets	CLASSDATA=
	Exclude from tables and output data sets all combinations of class variable values that are not in the CLASSDATA= data set	EXCLUSIVE
	Consider missing values as valid values for class variables	MISSING
Control the statistical analysis
	Exclude observations with nonpositive weights	EXCLNPWGTS
	Specify the sample size to use for the P2 quantile estimation method	QMARKERS=
	Specify the quantile estimation method	QMETHOD=
	Specify the mathematical definition to calculate quantiles	QNTLDEF=
	Specify the variance divisor	VARDEF=
Customize the appearance of the table
	Specify a default format for each cell in the table	FORMAT=
	Define the characters to use to construct the table outlines and dividers	FORMCHAR=
	Eliminate horizontal separator lines from the row titles and the body of the table	NOSEPS
	Order the values of a class variable according to the specified order	ORDER=
	Specify the default style element or style elements (for the Output Delivery System) to use for each cell of the table	STYLE=

Options

CLASSDATA=SAS-data-set

specifies a data set that contains the combinations of values of the class variables that must be present in the output. Any combinations of values of the class variables that occur in the CLASSDATA= data set but not in the input data set appear in each table or output data set and have a frequency of zero.

Restriction:	The CLASSDATA= data set must contain all class variables. Their data type and format must match the corresponding class variables in the input data set.
Interaction:	If you use the EXCLUSIVE option, PROC TABULATE excludes any observations in the input data set whose combinations of class variables is not in the CLASSDATA= data set.
Tip:	Use the CLASSDATA= data set to filter or supplement the input data set.
Featured in:	Specifying Class Variable Combinations to Appear in a Table

CONTENTS=link-name

allows you to name the link in the HTML table of contents that points to the ODS output of the first table that was produced using the TABULATE procedure.

Restrictions:

CONTENTS= has no effect on TABULATE procedure reports.

DATA=SAS-data-set

specifies the input data set.

Main Discussion:

Input Data Sets

EXCLNPWGTS

excludes observations with nonpositive weight values (zero or negative) from the analysis. By default, PROC TABULATE treats observations with negative weights like those with zero weights and counts them in the total number of observations.

Alias:	EXCLNPWGT
See also:	WEIGHT= and WEIGHT Statement

EXCLUSIVE

excludes from the tables and the output data sets all combinations of the class variable that are not found in the CLASSDATA= data set.

Requirement:	If a CLASSDATA= data set is not specified, this option is ignored.
Featured in:	Specifying Class Variable Combinations to Appear in a Table

FORMAT=format-name

specifies a default format for the value in each table cell. You can use any SAS or user-defined format.

Default:	If you omit FORMAT=, PROC TABULATE uses BEST12.2 as the default format.
Interaction:	Formats that are specified in a TABLE statement override the format that is specified with FORMAT=.
Tip:	This option is especially useful for controlling the number of print positions that are used to print a table.
Featured in:	Creating a Basic Two-Dimensional Table and Summarizing Information with the Universal Class Variable ALL

FORMCHAR <(position(s))>='formatting-character(s)'

defines the characters to use for constructing the table outlines and dividers.

position(s)

identifies the position of one or more characters in the SAS formatting-character string. A space or a comma separates the positions.

Default:	Omitting position(s) is the same as specifying all 20 possible SAS formatting characters, in order.
Range:	PROC TABULATE uses 11 of the 20 formatting characters that SAS provides. Formatting Characters Used by PROC TABULATE shows the formatting characters that PROC TABULATE uses. Formatting Characters in PROC TABULATE Output illustrates the use of each formatting character in the output from PROC TABULATE.

formatting-character(s)

lists the characters to use for the specified positions. PROC TABULATE assigns characters in formatting-character(s) to position(s), in the order that they are listed. For example, the following option assigns the asterisk (*) to the third formatting character, the pound sign (#) to the seventh character, and does not alter the remaining characters:

formchar(3,7)='*#'

Interaction:	The SAS system option FORMCHAR= specifies the default formatting characters. The system option defines the entire string of formatting characters. The FORMCHAR= option in a procedure can redefine selected characters.
Tip:	You can use any character in formatting-characters, including hexadecimal characters. If you use hexadecimal characters, you must put an `x` after the closing quote. For instance, the following option assigns the hexadecimal character 2D to the third formatting character, the hexadecimal character 7C to the seventh character, and does not alter the remaining characters: formchar(3,7)='2D7C'x
Tip:	Specifying all blanks for formatting-character(s) produces tables with no outlines or dividers. formchar(1,2,3,4,5,6,7,8,9,10,11) =' ' (11 blanks)
See also:	For more information on formatting output, see Chapter 5 "Controlling the Table's Appearance" in the SAS Guide to TABULATE Processing. For information on which hexadecimal codes to use for which characters, consult the documentation for your hardware.

Formatting Characters Used by PROC TABULATE
Position Default Used to draw

1 | the right and left borders and the vertical separators between columns

2 - the top and bottom borders and the horizontal separators between rows

3 - the top character in the left border

4 - the top character in a line of characters that separate columns

5 - the top character in the right border

6 | the leftmost character in a row of horizontal separators

7 + the intersection of a column of vertical characters and a row of horizontal characters

8 | the rightmost character in a row of horizontal separators

9 - the bottom character in the left border

10 - the bottom character in a line of characters that separate columns

11 - the bottom character in the right border

***Formatting Characters Used by PROC TABULATE***
Position	Default	Used to draw
1	\|	the right and left borders and the vertical separators between columns
2	-	the top and bottom borders and the horizontal separators between rows
3	-	the top character in the left border
4	-	the top character in a line of characters that separate columns
5	-	the top character in the right border
6	\|	the leftmost character in a row of horizontal separators
7	+	the intersection of a column of vertical characters and a row of horizontal characters
8	\|	the rightmost character in a row of horizontal separators
9	-	the bottom character in the left border
10	-	the bottom character in a line of characters that separate columns
11	-	the bottom character in the right border

Formatting Characters in PROC TABULATE Output

[IMAGE]

MISSING

considers missing values as valid values to create the combinations of class variables. Special missing values that are used to represent numeric values (the letters A through Z and the underscore (_) character) are each considered as a separate value. A heading for each missing value appears in the table.

Default:	If you omit MISSING, PROC TABULATE does not include observations with a missing value for any class variable in the report.
Main Discussion:	Including Observations with Missing Class Variables
See also:	SAS Language Reference: Concepts for a discussion of missing values that have special meaning.

NOSEPS

eliminates horizontal separator lines from the row titles and the body of the table. Horizontal separator lines remain between nested column headers.

Tip:	If you want to replace the separator lines with blanks rather than remove them, use the FORMCHAR= option .
Featured in:	Indenting Row Headings and Eliminating Horizontal Separators

NOTRAP

disables floating point exception (FPE) recovery during data processing. Note that normal SAS System FPE handling is still in effect so that PROC TABULATE terminates in the case of math exceptions.

Default:	FPE recovery is disabled.
Tip:	In operating environments where the overhead of FPE recovery is significant, NOTRAP can improve performance.
See also:	TRAP

ORDER=DATA | FORMATTED | FREQ | UNFORMATTED

specifies the sort order to create the unique combinations of the values of the class variables, which form the headings of the table, according to the specified order.

DATA

orders values according to their order in the input data set.

Interaction:

If you use PRELOADFMT in the CLASS statement, the order for the values of each class variable matches the order that PROC FORMAT uses to store the values of the associated user-defined format. If you use the CLASSDATA= option, PROC TABULATE uses the order of the unique values of each class variable in the CLASSDATA= data set to order the output levels. If you use both options, PROC TABULATE first uses the user-defined formats to order the output. If you omit EXCLUSIVE, PROC TABULATE appends after the user-defined format and the CLASSDATA= values the unique values of the class variables in the input data set based on the order that they are encountered.

Tip:

By default, PROC FORMAT stores a format definition in sorted order. Use the NOTSORTED option to store the values or ranges of a user defined format in the order that you define them.

FORMATTED

orders values by their ascending formatted values. This order depends on your operating environment.

Alias:

FMT | EXTERNAL

FREQ

orders values by descending frequency count.

Interaction:

Use the ASCENDING option in the CLASS statement to order values by ascending frequency count.

UNFORMATTED

orders values by their unformatted values, which yields the same order as PROC SORT. This order depends on your operating environment. This sort sequence is particularly useful for displaying dates chronologically.

Alias:

UNFMT | INTERNAL

Default:	UNFORMATTED
Interaction:	If you use the PRELOADFMT option in the CLASS statement, PROC TABULATE orders the levels by the order of the values in the user-defined format.
Featured in:	Understanding the Order of Headings with ORDER=DATA

OUT=SAS-data-set

names the output data set. If SAS-data-set doesn't exist, PROC TABULATE creates it.

The number of observations in the output data set depends on the number of categories of data that are used in the tables and the number of subtables that are generated. The output data set contains these variables (in this order):

by variables: variables listed in the BY statement.
class variables: variables listed in the CLASS statement.
_TYPE_: a character variable that shows which combination of class variables produced the summary statistics in that observation. Each position in _TYPE_ represents one variable in the CLASS statement. If that variable is in the category that produced the statistic, the position contains a 1; if it is not, the position contains a 0. In simple PROC TABULATE steps that do not use the universal class variable ALL, all values of _TYPE_ contain only 1's because the only categories that are being considered involve all class variables. If you use the variable ALL, your tables will contain data for categories that do not include all the class variables, and values of _TYPE_ will, therefore, include both 1's and 0's.
_PAGE_: The logical page that contains the observation.
_TABLE_: The number of the table that contains the observation.
statistics: statistics calculated for each observation in the data set.

Featured in:

Using Preloaded Formats with Class Variables

QMARKERS=number

specifies the default number of markers to use for the P² quantile estimation method. The number of markers controls the size of fixed memory space.

Default:	The default value depends on which quantiles you request. For the median (P50), *number* is 7. For the quartiles (P25 and P75), *number* is 25. For the quantiles P1, P5, P10, P90, P95, or P99, *number* is 107. If you request several quantiles, PROC TABULATE uses the largest default value of *number*.
Range:	an odd integer greater than 3
Tip:	Increase the number of markers above the default settings to improve the accuracy of the estimates; reduce the number of markers to conserve memory and computing time.
Main Discussion:	Quantiles

QMETHOD=OS|P2

specifies the method PROC TABULATE uses to process the input data when it computes quantiles. If the number of observations is less than or equal to the QMARKERS= value and QNTLDEF=5, both methods produce the same results.

OS

uses order statistics. This is the technique that PROC UNIVARIATE uses.

Note: This technique can be very memory-intensive. [cautionend]

P2

uses the P² method to approximate the quantile.

Default:	OS
Restriction:	When QMETHOD=P2, PROC TABULATE does not compute weighted quantiles.
Tip:	When QMETHOD=P2, reliable estimates of some quantiles (P1,P5,P95,P99) may not be possible for some types of data.
Main Discussion:	Quantiles

QNTLDEF=1|2|3|4|5

specifies the mathematical definition that the procedure uses to calculate quantiles when QMETHOD=OS is specified. When QMETHOD=P2, you must use QNTLDEF=5.

Default:	5
Alias:	PCTLDEF=
Main discussion:	Percentile and Related Statistics

STYLE=<style-element-name | <PARENT>><[style-attribute-specification(s)]>

specifies the style element to use for the data cells of a table when it is used in the PROC TABULATE statement. For example, the following statement specifies that the background color for data cells be red:

proc tabulate data=one style=[background=red];

Note: This option can be used in other statements, or in dimension expressions, to specify style elements for other parts of a table. [cautionend]

Note: You can use braces ({ and }) instead of square brackets ([ and ]). [cautionend]

style-element-name

is the name of a style element that is part of a style definition that is registered with the Output Delivery System. SAS Institute provides some style definitions. Users can create their own style definitions with PROC TEMPLATE.

Default:	If you do not specify a style element, PROC TABULATE uses Data.
See also:	For information about Institute-supplied style definitions, see What Style Definitions Are Shipped with the Software? . For information about PROC TEMPLATE and the Output Delivery System, see The Complete Guide to the SAS Output Delivery System.

PARENT

specifies that the data cell use the style element of its parent heading. The parent style element of a data cell is one of the following:

the style element of the leaf heading above the column that contains the data cell, if the table specifies no row dimension, or if the table specifies the style element in the column dimension expression.
the style element of the leaf heading above the row that contains the cell, if the table specifies the style element in the row dimension expression.
the Beforecaption style element, if the table specifies the style element in the page dimension expression.
undefined, otherwise.

Note: The parent of a heading (not applicable to STYLE= in the PROC TABULATE statement) is the heading under which the current heading is nested. [cautionend]

style-attribute-specification(s)

describes the attribute to change. Each style-attribute-specification has this general form:

style-attribute-name=style-attribute-value

You can set or change the following attributes with the STYLE= option in the PROC TABULATE statement (or in any other statement that uses STYLE=, except for the TABLE statement):

ASIS= FONT_WIDTH=

BACKGROUND= HREFTARGET=

BACKGROUNDIMAGE= HTMLCLASS=

BORDERCOLOR= JUST=

BORDERCOLORDARK= NOBREAKSPACE=

BORDERCOLORLIGHT= POSTHTML=

BORDERWIDTH= POSTIMAGE=

CELLHEIGHT= POSTTEXT=

CELLWIDTH= PREHTML=

FLYOVER= PREIMAGE=

FONT= PRETEXT=

FONT_FACE= PROTECTSPECIALCHARS=

FONT_SIZE= TAGATTR=

FONT_STYLE= URL=

FONT_WEIGHT= VJUST=

For more information about style attributes, see What Style Attributes Can Base Procedures Specify? .

Alias:	S=
Restriction:	This option affects only the HTML and Printer output.
Tip:	To specify a style element for data cells with missing values, use STYLE= in the TABLE statement MISSTEXT= option.
See also:	Using Style Elements in PROC TABULATE
Featured in:	Specifying Style Elements for HTML Output

TRAP

enables floating point exception (FPE) recovery during data processing beyond that provided by normal SAS System FPE handling, which terminates PROC TABULATE in the case of math exceptions.

Default:	FPE recovery is disabled.
Tip:	Remove TRAP or use NOTRAP to improve performance in operating environments where the overhead of FPE recovery is significant.
See also:	NOTRAP

VARDEF=divisor

specifies the divisor to use in the calculation of the variance and standard deviation. Possible Values for VARDEF= shows the possible values for divisor and the associated divisors.

Possible Values for VARDEF=
Value Divisor Formula for Divisor

DF degrees of freedom n - 1

N number of observations n

WDF sum of weights minus one ( [Sigma] _iw_i) - 1

WEIGHT|WGT sum of weights [Sigma] _iw_i

The procedure computes the variance as [IMAGE] , where [IMAGE] is the corrected sums of squares and equals [IMAGE] . When you weight the analysis variables, [IMAGE] equals [IMAGE] where [IMAGE] is the weighted mean.

Default: DF
Requirement: To compute standard error of the mean, use the default value of VARDEF=.
Tip: When you use the WEIGHT statement and VARDEF=DF, the variance is an estimate of [IMAGE] , where the variance of the ith observation is [IMAGE] , and [IMAGE] is the weight for the ith observation. This yields an estimate of the variance of an observation with unit weight.
Tip: When you use the WEIGHT statement and VARDEF=WGT, the computed variance is asymptotically (for large n) an estimate of [IMAGE] , where [IMAGE] is the average weight. This yields an asymptotic estimate of the variance of an observation with average weight.
See also: the example of weighted statistics .

***Possible Values for VARDEF=***
Value	Divisor	Formula for Divisor
DF	degrees of freedom	n - 1
N	number of observations	n
WDF	sum of weights minus one	(_iw_i) - 1
WEIGHT\|WGT	sum of weights	_iw_i

Chapter Contents
Previous
Next
Top of Page

ASIS=	FONT_WIDTH=
BACKGROUND=	HREFTARGET=
BACKGROUNDIMAGE=	HTMLCLASS=
BORDERCOLOR=	JUST=
BORDERCOLORDARK=	NOBREAKSPACE=
BORDERCOLORLIGHT=	POSTHTML=
BORDERWIDTH=	POSTIMAGE=
CELLHEIGHT=	POSTTEXT=
CELLWIDTH=	PREHTML=
FLYOVER=	PREIMAGE=
FONT=	PRETEXT=
FONT_FACE=	PROTECTSPECIALCHARS=
FONT_SIZE=	TAGATTR=
FONT_STYLE=	URL=
FONT_WEIGHT=	VJUST=

Default:	DF
Requirement:	To compute standard error of the mean, use the default value of VARDEF=.
Tip:	When you use the WEIGHT statement and VARDEF=DF, the variance is an estimate of , where the variance of the ith observation is , and is the weight for the ith observation. This yields an estimate of the variance of an observation with unit weight.
Tip:	When you use the WEIGHT statement and VARDEF=WGT, the computed variance is asymptotically (for large n) an estimate of , where is the average weight. This yields an asymptotic estimate of the variance of an observation with average weight.
See also:	the example of weighted statistics .