The TABULATE Procedure

PROC TABULATE Statement

PROC TABULATE <option(s)>;

Task		Option
Customize the HTML contents link to the output		CONTENTS=
Specify the input data set		DATA=
Specify the output data set		OUT=
Override the SAS system option THREADS \| NOTHREADS		THREADS \| NOTHREADS
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
	Specify the confidence level for the confidence limits	ALPHA=
	Exclude observations with nonpositive weights	EXCLNPWGT
	Specify the sample size to use for the P² 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

ALPHA=value

specifies the confidence level to compute the confidence limits for the mean. The percentage for the confidence limits is (1-value)×100. For example, ALPHA=.05 results in a 95% confidence limit.

Default:	.05
Range:	between 0 and 1
Interaction:	To compute confidence limits specify the statistic-keyword LCLM or UCLM.

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, then PROC TABULATE excludes any observations in the input data set whose combinations of values of class variables are 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

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

Note: CONTENTS= affects only the contents file of ODS HTML output. It has no effect on the actual TABULATE procedure reports. [cautionend]

DATA=SAS-data-set

specifies the input data set.

Main Discussion:

Input Data Sets

EXCLNPWGT

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

Alias:	EXCLNPWGTS
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, then 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.

Alias:	F=
Default:	If you omit FORMAT=, then 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 in which 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.
Restriction:	The FORMCHAR= option affects only the traditional SAS monospace output destination.
Tip:	You can use any character in formatting-characters, including hexadecimal characters. If you use hexadecimal characters, then you must put x after the closing quotation mark. For example, the following option assigns the hexadecimal character 2D to the third formatting character, assigns 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 about formatting output, see Chapter 5, "Controlling the Table's Appearance," in the SAS Guide to TABULATE Processing. For information about 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 in PROC TABULATE Output

[Formatting Characters in PROC TABULATE Output]

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, then 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:	Special Missing Values in 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 headings.

Restriction:	The NOSEPS option affects only the traditional SAS monospace output destination.
Tip:	If you want to replace the separator lines with blanks rather than remove them, then use option FORMCHAR=.
Featured in:	Indenting Row Headings and Eliminating Horizontal Separators

NOTHREADS

See THREADS | NOTHREADS.

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, then 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, then 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, then PROC TABULATE first uses the user-defined formats to order the output. If you omit EXCLUSIVE, then PROC TABULATE appends after the user-defined format and the CLASSDATA= values the unique values of the class variables in the input data set in the same order in which 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 in which you define them.

FORMATTED

orders values by their ascending formatted values. If no format has been assigned to a numeric class variable, then the default format, BEST12., is used. 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, then 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 does not exist, then 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 that are listed in the BY statement.
class variables: variables that are 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, then the position contains a 1. Otherwise, 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 1s because the only categories that are being considered involve all class variables. If you use the variable ALL, then your tables will contain data for categories that do not include all the class variables, and positions of _TYPE_ will, therefore, include both 1s and 0s.
_PAGE_: The logical page that contains the observation.
_TABLE_: The number of the table that contains the observation.
statistics: statistics that are calculated for each observation in the data set.

Featured in:

Using Preloaded Formats with Class Variables

PCTLDEF=

See QNTLDEF=.

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 105. If you request several quantiles, then 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|HIST

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, then both methods produce the same results.

OS

uses order statistics. PROC UNIVARIATE uses this technique.

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

P2|HIST

uses the P² method to approximate the quantile.

Default:	OS
Restriction:	When QMETHOD=P2, PROC TABULATE will not compute the following items: MODE weighted quantiles
Tip:	When QMETHOD=P2, reliable estimates of some quantiles (P1, P5, P95, P99) might 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:	Quantile and Related Statistics

STYLE=<style-element-name|<PARENT>>[style-attribute-name=style-attribute-value<... style-attribute-name=style-attribute-value>]

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=[backgroundcolor=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 provides some style definitions. You can create your own style definitions with PROC TEMPLATE.

Default:	If you do not specify a style element, then PROC TABULATE uses Data.
See also:	Concepts: Style Definitions and the TEMPLATE Procedure in SAS Output Delivery System: User's Guide for information about PROC TEMPLATE and the default style definitions. For information about the style elements, see ODS Style Elements in SAS Output Delivery System: User's Guide.

<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: In this usage, the angle brackets around the word PARENT are required. Curly braces or square brackets cannot be substituted in the syntax. [cautionend]

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-name

specifies the attribute to change. The following table shows attributes that you can set or change with the STYLE= option in the PROC TABULATE statement (or in any other PROC TABULATE statement that uses the STYLE= option, except for the TABLE statement). Note that not all attributes are valid in all destinations.

Style Attributes for PROC REPORT and PROC TABULATE
Attribute	PROC REPORT STATEMENT: REPORT Area	PROC REPORT Areas: CALLDEF, COLUMN, HEADER, LINES, SUMMARY	PROC TABULATE STATEMENT: TABLE	PROC TABULATE STATEMENTS: VAR, CLASS, BOX Opt, CLASSLEV, KEYWORD
ASIS=	X	X		X
BACKGOUNDCOLOR=	X	X	X	X
BACKGOUNDIMAGE=	X	X	X	X
BORDERBOTTOMCOLOR=	X	X		X
BORDERBOTTOMSTYLE=	X	X	X	X
BORDERBOTTOMWIDTH=	X	X	X	X
BORDERCOLOR=	X	X		X
BORDERCOLORDARK=	X	X	X	X
BORDERCOLORLIGHT=	X	X	X	X
BORDERTOPCOLOR=	X	X		X
BORDERTOPSTYLE=	X	X	X	X
BORDERTOPWIDTH=	X	X	X	X
BORDERWIDTH=	X	X	X	X
CELLPADDING=	X		X
CELLSPACING=	X		X
CLASS=	X	X	X	X
COLOR=	X	X	X
FLYOVER=	X	X		X
FONT=	X	X	X	X
FONTFAMILY=	X	X	X	X
FONTSIZE=	X	X	X	X
FONTSTYLE=	X	X	X	X
FONTWEIGHT=	X	X	X	X
FONTWIDTH=	X	X	X	X
FRAME=	X		X
HEIGHT=	X	X		X
HREFTARGET=		X		X
HTMLSTYLE=	X	X	X	X
NOBREAKSPACE=	X	X		X
POSTHTML=	X	X	X	X
POSTIMAGE=	X	X	X	X
POSTTEXT=	X	X	X	X
PREHTML=	X	X	X	X
PREIMAGE=	X	X	X	X
PRETEXT=	X	X	X	X
PROTECTSPECIALCHARS=		X		X
RULES=	X		X
TAGATTR=	X	X		X
TEXTALIGN=	X	X	X	X
URL=		X		X
VERTICALALIGN=		X		X
WIDTH=	X	X	X	X

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

Alias:	S=
Restriction:	This option affects only the HTML, RTF, and Printer destinations.
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 ODS Output

Default:	value of SAS system option THREADS \| NOTHREADS.
Restriction:	Your site administrator can create a restricted options table. A restricted options table specifies SAS system option values that are established at startup and cannot be overridden. If the THREADS \| NOTHREADS system option is listed in the restricted options table, any attempt to set these system options is ignored and a warning message is written to the SAS log.
Interaction:	PROC TABULATE uses the value of the SAS system option THREADS except when a BY statement is specified or the value of the SAS system option CPUCOUNT is less than 2. In those cases, you can specify the THREADS option in the PROC TABULATE statement to force PROC TABULATE to use parallel processing. Note: When multi-threaded processing, also known as parallel processing, is in effect, observations might be returned in an unpredictable order. However, the observations are sorted correctly if a BY statement is specified.

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:	Weighted Statistics Example