Tabulate

Syntax

TABULATE
     FILENAME = filename
     HEADER   = (expression)
    [STUB     = (expression)]
    [WAFER    = (expression)]

Expressions

TABULATE  HEADER = (expression) [STUB = (expression) [WAFER = (expression)]]

TABULATE HEADER = (RACE)  STUB = (REGION)
TABULATE HEADER = (RACE THEN SEX) STUB = (REGION)
TABULATE HEADER = (SALARY BY (MEAN THEN MEDIAN)) STUB = (REGION BY RACE)

Nesting variables with the BY operator also produces a cross-tabulation within a single dimension. If the same variables are used as a Stub and header or as a header with a BY clause, the same cells are produced but the table has a different shape.

If the tabulate statement contains a WAFER expression, a set of two-dimensional tables is produced. There must be a stub expression for a WAFER expression to be valid. When more than one wafer is generated a table of contents is also produced. If a particular wafer contains no data, it is not printed and is marked deleted in the table of contents. If only one wafer is produced, it is printed even if it is empty. For example:

TABULATE HEADER = (SEX)           STUB = (AGE)         WAFER = (REGION)
TABULATE HEADER = (SEX)           STUB = (AGE)         WAFER = (REGION BY RACE)
TABULATE HEADER = (AGE BY REGION) STUB = (RACE BY SEX) WAFER = (COUNT THEN PCT)

BY

Example: BY

HEADER = (SEX BY AGE BY INCOME)

THEN

Example: THEN

HEADER = (AGE THEN SEX)

When two variables are joined by THEN, each value of each variable defines a cell. The number of cells produced is the sum of the number of values for each variable.

Combining BY and THEN

Example: BY and THEN

HEADER = (AGE THEN INCOME BY SEX)

The next example concatenates AGE and INCOME and nests SEX within both AGE and INCOME, using parentheses to cause the concatenation operator to be applied first.

HEADER = ((AGE THEN INCOME) BY SEX)

Other combinations might be:

HEADER = (AGE BY INCOME THEN SEX)

HEADER = (AGE BY (INCOME THEN SEX))

Nesting and concatenation can also be used in the stub and wafer expressions.

Variable Types

Cell Contents

Control variables provide classifications which determine the structure of the table and the presence of a particular cell. Observation variables give the result that appears in the cell.

When two control variables are cross tabulated or nested, the content of the cell is a count of the number of occurrences of data which match that combination of values.

When an observation variable is cross tabulated or nested with a control variable, the total (sum) for the observation variable is the default. That is, the structure of the table is unaltered by the inclusion of an observation variable but, instead of a count of data appearing in the cell contents, a total of the observation variable is calculated and an appropriate header label printed. Other statistics can be specified for observation variables.

Two observation variables cannot be cross tabulated or nested within each other and therefore observation variables may be specified in one and only one expression (dimension) of the TABULATE statement.

Different types of information are reported in the cell contents for Control and Observation variables. For Control variables, which have discrete values, counts or percentages are reported. For Observation variables, which have continuous values, a variety of cell statistics are reported, the default being a sum of the values. For any cell that cross tabulates a control and an observation variable, the observation variable statistics are reported.

Example: Observation Variables

HEADER = (SALARY BY AGE BY SEX) STUB = (REGION)

HEADER = (SEX THEN (SALARY BY SEX) STUB = (AGE)

HEADER = (AGE BY (SEX THEN SALARY)) STUB = (REGION)

Variable Modifiers

variable[.modifier][.modifier]...

The .FIRST modifier is used in conjunction with the percent base marker as described in the section on percentages. It specifies that the first occurrence of the percent base marker is used.

The .TOTAL modifier automatically concatenates a TOTAL control variable as described in the section on TOTAL. The TOTAL is displayed before the variable being modified.

HEADER = (AGE.T.U)

HEADER = (AGE BY SEX) STUB = (REGION)

HEADER = (AGE.S BY SEX.S) STUB = (REGION.S)

Statistics

TABULATE HEADER = (N THEN SALARY BY (MEAN THEN STDEV)) STUB = (SEX)
         TOTAL  = N 'Valid Cases'
         MEAN   = MEAN 'X Bar'
         STDEV  = STDEV 'Sigma'

The pseudo-variables created can be used anywhere that is valid. If the pseudo-variable is a TOTAL it is a control variable, otherwise it is an observation variable. Multiple pseudo-variables can be created and assigned to the same keyword. In the next example, both N and SUM are totals, N is the count of records and SUM is the total SALARY.

TABULATE HEADER = (N THEN SALARY BY (SUM THEN MEAN THEN STDEV)) STUB = (SEX)
         TOTAL = N 'Valid Cases'  SUM 'Sum'
         MEAN  = MEAN 'X Bar'
         STDEV = STDEV 'Sigma'

Example: Statistical keywords

HEADER = (SALARY BY (MIN THEN MAX)) STUB = (SEX)

Keywords must be nested within an observation variable. Keywords may not be nested within each other; they may be concatenated. For example, SALARY BY MEAN BY STDEV is not allowed; SALARY BY (MEAN THEN STDEV) is allowed.

Accuracy and ISDNUMBER

ISDNUMBER = n /

TABULATE HEADER    = (SALARY BY (MEAN THEN Q)) STUB = (TOTAL THEN REGION)
         ISDNUMBER = 5
         QUANTILES = Q 'Quantiles' 4 

Total

Example: TOTAL

HEADER = (AGE THEN TOTAL)

The control variable, AGE, with two classifications is concatenated with TOTAL. The total column equals the count of all occurrences at the appropriate level of nesting. (Note that records with undefined values have still been counted.)

HEADER = (SALARY THEN TOTAL)

SALARY is an observation variable and thus the sum is displayed by default. The keyword TOTAL is a control variable and displays a count. Cross tabulating or nesting TOTAL with either control or observation variables is allowed since it is a control variable. As per the standard rules for cell contents, if TOTAL is nested with a control variable, a count is produced; if it is nested with an observation variable, a sum of the observation variable is produced.

Example: TOTAL with BY and THEN

HEADER = (SALARY BY AGE BY (TOTAL THEN SEX))

Since TOTAL is nested within an observation variable, this column contains the total SALARY for each AGE group.

TOTAL shorthand notation

TOTAL THEN AGE
AGE.T

TABULATE  HEADER = (SALARY BY AGE BY (TOTAL THEN SEX))
TABULATE  HEADER = (SALARY BY AGE BY SEX.T)

Normalized and Quantiles

In the first form, the quantity "n" specifies the number of equal-sized quantiles to be produced. Of these, n-1 are printed. Thus for n=4, TABULATE prints output for the 25%, 50% and 75% quantiles. In the second form a number of non-equal sized quantiles are produced and each n specifies the quantiles to produce. For example, specifying Ns of 15 and 85 produces the 15th and 85th percentage quantiles. Both forms allow specification of labels. In the first form, when labels are used for individual quantiles, the commas separating 'label1', 'label2', etc. are required.

1)  keyword = variable ['label'] n ['label1', ...]
2)  keyword = variable ['label'] (n1) ['label'] (n2)...

Example: Quantiles

TABULATE HEADER = (SALARY BY (MEAN THEN Q)) STUB = (REGION.T)
         QUANTILES = Q 'Quantiles' 4

A specification in the first form , with multiple labels:

TABULATE  HEADER    = (SALARY BY (MEAN THEN Q)) STUB = (REGION.T)
          QUANTILES = Q 'Quantiles' 4 '25%', 'Median', '75%'

A specification in the second form:

TABULATE HEADER    = (SALARY BY (MEAN THEN Q)) STUB = (REGION.T)
         QUANTILES = D 'Deciles' (10) '10th' (50) '50th' (90) '90th'

Percentages

Tables can be specified which contain row percentages, column percentages or overall percentages. Multiple percentages can be displayed in a single table. A single table can have percentages computed from more than one base value. However a table cannot have any cell which is an intersect of two percentage calculations. This means that all percentage cells must appear in a single expression; there cannot be percentages in both a row and column expression.

TOTAL%X THEN REGION
REGION%X

TABULATE HEADER = (TOTAL%X THEN REGION) STUB = (X BY (TOTAL%X THEN AGE))
TABULATE HEADER = (REGION%X)            STUB = (X BY AGE%X)

A brief review of .T and %X notation: An expression such as AGE.T, produces three rows or columns - TOTAL,Young and Old. An expression such as REGION%X, produces three categories - TOTAL, North and South. It also specifies the TOTAL column as the percent base. The sub-expression, X.T, is the same as TOTAL THEN X and produces two cells for each category.

Percentages with Counts

HEADER = (TOTAL THEN REGION) STUB = (TOTAL THEN AGE)

To add percentages to this table, define a pseudo-variable, say X, and reference this on PERCENT clause, for example:

PERCENT = X 'Percent'

Decide where to place the percent variable X, ensuring that it is nested correctly. Specifying it in the header expression or in the stub expression only affects the labeling of the table.

Row Percents

TABULATE HEADER  = (X BY (TOTAL%X THEN REGION)) STUB = (TOTAL THEN AGE)
         PERCENT =  X 'Percent'

The total (100%) and regional percentages for each age group (row) are displayed in the table. The table contains only percentages because the percent variable, X, is at the highest level of the header expression nesting. The remainder of the header, (TOTAL THEN REGION), is nested within X. The percent marker, %X, specifies the header column TOTAL as the 'set' of base cells for this table's percent calculations. Each cell value for TOTAL is the base for the percents in its row. For example, the calculations for the first row of the table are:

TOTAL  = 2200/2200 * 100 = 100
         North  = 1200/2200 * 100 = 55
         South  =  800/2200 * 100 =  41

Moving X to the stub expression changes the labeling of the table but not its contents:

TABULATE HEADER = (TOTAL%X THEN REGION) STUB = (X BY (TOTAL THEN AGE))
         PERCENT = X 'Percent'

Column Percents

In the example, move the base marker, %X, from TOTAL in the header and append it to TOTAL in the stub:

TABULATE HEADER = (X BY (TOTAL THEN REGION)) STUB = (TOTAL%X THEN AGE)
         PERCENT = X 'Percent'

As in the preceding table, the percent variable, X, is nested with all cells of the table so that all cells contain percents. In this table, however, the percent marker, %X, specifies the TOTAL row in the stub expression as the set of base cells for this table's percent calculations. For example, the calculations for the first column of the table are:

          TOTAL            = 2200/2200 * 100 = 100
          Young            = 600/2200 * 100 =  45
          Old              = 1400/2200 * 100 =  50

Base Markers in Both the Header and the Stub

• If the percent base marker is appended to a total column in the header expression, row percentages are produced.

• If the percent base marker is appended to a total row in the stub expression, column percentages are produced.

TABULATE  HEADER = (X BY (TOTAL%X THEN REGION)) STUB = (TOTAL%X THEN AGE)
          PERCENT = X 'Percent'

In this table, all percentages are measured using the first cell of the table as the base. This cell is at the intersection of the stub TOTAL row and the header TOTAL column.

Percentages with Observation Variables

Example: Percentages for Observation Variables

TABULATE  HEADER = (SALARY BY (TOTAL THEN AGE))

The corresponding percentage table is:

TABULATE  HEADER = (X BY SALARY BY (TOTAL%X THEN AGE))
PERCENT = X 'Percent' /

Moving the clause (TOTAL%X THEN AGE) to the front of the expression, results in the same data in the table with different labeling:

TABULATE HEADER = ((TOTAL%X THEN AGE) BY X BY SALARY)
         PERCENT = X 'Percent'

Original Values and Percents

The following has alternating columns of original values and percents. Note only the cells nested within the percent variable X contain percents.

TABULATE HEADER  = (REGION%X BY X.T) STUB = (AGE.T)
         PERCENT =  X 'Pct' /

Instead of presenting the data in alternating columns, it could be grouped into separate blocks of frequencies and percents by inverting the order of the header expression.

TABULATE HEADER  = (X.T BY REGION%X) STUB = (AGE.T)
         PERCENT =  X 'Pct' /

Percents for Row Totals.

TABULATE HEADER  = (TOTAL THEN (X BY REGION%X)) STUB = (AGE.T)
         PERCENT =  X 'Pct' /

An analogous set of tables can be designed for percentages and totals of an observation variable. The following table displays alternating columns of total salary and percentage.

TABULATE HEADER  = (SALARY BY SEX%X BY X.T STUB) = (AGE.T)
         PERCENT =  X 'Pct'

This table groups all salary columns followed by all the percent columns.

TABULATE HEADER  = (SALARY BY X.T BY SEX%X) STUB = (AGE.T)
         PERCENT =  X 'Pct'

The following shows total salary for both sexes and percentage by sex.

TABULATE HEADER  = (SALARY BY (TOTAL THEN (X BY SEX%X))) STUB = (AGE.T)
         PERCENT =  X 'Pct'

Percentages in Wafers

TABULATE HEADER  = (REGION%X) STUB = (AGE%X) WAFER = (X.T)
         PERCENT =  X 'Percent'

HEADER  = (REGION%X) STUB = (AGE%X) WAFER = (X.T)
Table of Contents:
TOTAL........................................  page 1
Percent......................................  page 2

HEADER  = (REGION%X) STUB = (AGE%X) WAFER = (X.T)
TOTAL..............

(On next page)

HEADER = (REGION%X) STUB = (AGE%X) WAFER = (X.T)
Percent............

Example percentage tables

TABULATE HEADER = (REGION.T) STUB = (AGE.T BY SEX.T)

Base marker appended to AGE

TABULATE HEADER = (X BY REGION.T)  STUB = (AGE%X BY SEX.T)  PERCENT = X 'Percent'

Base marker appended to SEX

TABULATE HEADER = (X BY REGION.T) STUB = (AGE BY SEX%X) PERCENT = X 'Percent'

Use of FIRST

TABULATE HEADER  = (X BY REGION.T) STUB = (AGE BY SEX%X.F)
         PERCENT =  X 'Percent'

Multiple Base Markers

TABULATE HEADER  = (X BY REGION%X) STUB = (AGE%X BY SEX.T)
         PERCENT =  X 'Percent'

TABULATE HEADER  = (X BY REGION%X) STUB = (AGE BY SEX%X)
         PERCENT = X 'Percent'

TABULATE HEADER  = (X BY REGION%X) STUB = (AGE BY SEX%X.F)
         PERCENT = X 'Percent'

Multiple Percent Variables Within a Table

PERCENT=  PR 'Row Pct' PC 'Col Pct' /

TABULATE HEADER  = ((PR + PC) BY REGION%PR) STUB = (AGE%PC)
         PERCENT = PR 'Row Pct' PC 'Col Pct'

Multiple percents in Rows

TABULATE HEADER  = (REGION%PR) STUB = (AGE%PC BY (PR + PC))
         PERCENT = PR 'Row Pct' /
         PERCENT = PC 'Col Pct' /

Example: Overall Percents

TABULATE HEADER =  (REGION%PR%PT) STUB = (AGE%PC%PT BY (PR + PC + PT))
         PERCENT = PR 'Row Pct'
         PERCENT = PC 'Col Pct'
         PERCENT = PT 'Tot Pct'

Counts and Multiple Percents

TABULATE HEADER = (REGION%PR%PT) STUB = (AGE%PC%PT BY (TOTAL + PR + PC + PT))
         TOTAL = TOTAL 'Count'
         PERCENT = PR 'Row Pct'
         PERCENT = PC'Col Pct'
         PERCENT = PT 'Tot Pct'

Percentages in both Columns and Rows

Suppose the table is a breakdown of males and females under and over forty. The basic table (without percentages) is easily produced with a table statement such as:

TABULATE HEADER = (AGEGROUP.T)  STUB = (GENDER.T)

It is simple to add percentages to either the row totals, giving percentages of males and females or the column totals giving percentages of age groups but not both at once. The solution is to produce two tables in the one run:

TABULATE HEADER =(TOTY THEN AGEGROUP THEN X) STUB = (TOTX%X THEN GENDER)
         FILENAME = A
         TOTAL = TOTX 'Total Males and Females'
         PERCENT = X '% BY AGE'
TABULATE HEADER = (TOTY%Y THEN AGEGROUP) STUB = (Y)
         TOTAL = TOTY 'Total Young and Old'
         PERCENT = Y '% BY GENDER'

Record Filtering

BOOLEAN  = (logical expression)
SAMPLE   = fraction
WEIGHT   = varname

RACE=1   REGION=2   FAMSIZE =5

Print Formatting

PRINTFORMATS = var list (options,...) .../

COMMAS (C)	Separates groups of 3 digits by commas.
DECIMAL=n (n)	Specifies the number of decimal places to be printed. A decimal point is only printed when the number of decimal places is non-zero.
DOLLAR (D)	Places a floating dollar sign before the number.
PERCENT (P)	Places a trailing percent sign after the number.

By default the DOLLAR, PERCENT and COMMAS options are not in effect and DECIMAL= 0 (i.e. numbers are printed as integers).

Use of the PRINTFORMATS clause.

TABULATE HEADER = (SALARY BY (TOTAL THEN (X BY SEX%X))) STUB = (AGE.T)
         PERCENT = X 'Pct' /
         PRINTFORMATS = SALARY (D,C) X (P,2) /

TOTAL under SALARY is printed with dollar signs and commas. However, TOTAL under PCT is printed with a percent sign and two decimal places because of the precedence rules for print formats.

Precedence rules for print formats

• Except for percents, variables that have no assigned PRINTFORMAT are not considered in the precedence analysis.
• PRINTFORMATS for percentages have the highest precedence. If a percent variable is not assigned an explicit PRINTFORMAT, it is printed as an integer.

• In an expression containing an observation variable nested with a control variable, the observation variable's PRINTFORMAT takes precedence.

• When a statistical keyword (e.g. MEAN, MAX, etc.) is nested with an observation variable, the observation variable's PRINTFORMAT is used. When nesting variables, the PRINTFORMAT of the variable lower in the nesting takes precedence.

TABULATE HEADER  = (SALARY BY REGION BY (MIN THEN MAX))
         MINIMUM = MIN 'Lowest'
         MAXIMUM = MAX 'Highest'
         PRINTFORMATS = SALARY (D) MIN (2)MAX (2)

TABULATE HEADER = (REGION BY (MIN THEN MAX) BY SALARY)

• When an expression contains two or more nested control variables, the PRINTFORMAT of the lowest variable in the nesting takes precedence.

• For the purposes of PRINTFORMAT precedence, the header expression has highest precedence followed by the stub and then the wafer.

Observation variables are displayed in a summary cell labeled with the variable label. If there is no variable label, the variable name is used.

Page Formatting

PAGETITLE = 'string'['string'['string']]
PAGELENGTH = n
PAGEWIDTH = n
COLLAPSE

PAGETITLE	Specifies the title printed left justified at the top of the page. By default, the page title is the string "SIR/XS Tabulate Procedure" and the page number is printed at the top right-hand side of the page. The page title may consist of up to 3 lines. The first line contains the page number. The second and third lines are printed if there are wafers and/or if the wafers must be broken into chunks (i.e. the wafer does not fit on a single page).
PAGELENGTH	The maximum number of lines that can appear on a page. Specifying PAGELENGTH = NOEJECT turns off paging. The default PAGELENGTH value is the current output file page length. This must include space for: the page title and page number line(s), the wafer title, wafer label, and the blank lines between, the table (wafer) itself and any WAFERNOTES specified.
PAGEWIDTH	Controls the width of the printed page. By default, tables are printed centred within PAGEWIDTH print positions. The default value is the current output file page width.
COLLAPSE	If a wafer exceeds either PAGELENGTH or PAGEWIDTH, an attempt is made to break the wafer into multiple 'chunks', each printed on a separate page. The chunks appear in the output in order from left to right, then top to bottom. The wafer and stub labels appear as needed. The COLLAPSE option allows chunks of broken up wafers to be printed on the same page. No more than 2 chunks are printed per page. The default is not to collapse chunks into a single page.

Header Formatting

HEADERWIDTH = n
HEADERINDENTATION = n
HEADERDIVIDER = 'character'
NODIVIDERS
NOHEADERCENTER

HEADERWIDTH	Specifies the width to be used in printing each column of the table. This width does not include the column divider. The default header width is 10 print positions. If HEADERWIDTH is set too small, there may be insufficient room to print a number in the table, in which case the field is filled with Xs. Also, if HEADERWIDTH is too small, there may not be enough room for the header labels to be printed and they may be severely segmented. Conversely, if HEADERWIDTH is set too large, the table header may contain too many print positions for one page. When this occurs, the page is broken into chunks. Chunks are printed on successive pages left to right, top to bottom.
HEADERINDENTATION	Specifies the number of print lines for each level of the header nesting. The default is 3 and it cannot be set smaller.
HEADERDIVIDER	Specifies the character used as the column divider. The default is a vertical bar (|). This character is used for both the label area of the header as well as between the columns of data.
NODIVIDERS	Sets the divider character between columns of data to blank. It does not affect the label area of the header.
NOHEADERCENTER	Left justifies header labels in a column. By default header labels are centred in the column.

Stub Formatting

STUBTITLE = option 'string'
STUBWIDTH = n
STUBINDENTATION = n
STUBCONTINUATION = n
STUBHEIGHT = n
STUBFILLER = 'character'
STUBDIVIDER = 'character'

TABULATE STUB = (AGE BY SEX) HEADER = (REGION.T)
         STUBTITLE = 'Age by Sex'
         STUBWIDTH = 20

TABULATE STUB = (AGE BY SEX) HEADER = (REGION.T
         STUBFILLER = ' '

Wafer Formatting

WAFERTITLE = option 'string'
NOWAFERTITLE
WAFERCENTER
WAFERSTART = n
WAFERINDENTATION = n
WAFERCONTINUATION = n
WAFERHEIGHT = n

TABULATE  WAFER = (AGE BY REGION) STUB = (SEX) HEADER = (RACE)
         Under 21
          North.......

         Under 21
          South.......

         21 and Over
           North.......

         21 and Over
           South.......

Other Options

HTML

When output is in html format, then the print formatting, page formatting and many of the general output control options can be specified but have no effect. There is no control over the detailed appearance of the table. The general shape of the table is dictated by the table expressions and the specifics of the table appearance depend on the software used to view and/or print the resulting html.

Footnotes

Zero Printing

Converting Text to Uppercase

Example: Dummy Spanner Labels

TABULATE HEADER = (J BY (SEX THEN REGION))
         DUMMY  = J 'Sex and region Data'

Language

'segment' option 'segment'

Plus (+) simply concatenates two segments; blank forces a line break between the two segments; minus (-) specifies optional segmentation as per the following rules.

• Forced break.

• Conditional break.

• Space, hyphen, or underscore.

• Vowels. If a vowel is found, the break occurs after the vowel, with that segment followed by a hyphen when printed.

After the first line segment has been formed, all the remaining characters of the original label to the right of the break character are considered next. If the remaining segment fits in the allocated space, it is printed, otherwise, the first n characters of this portion of the label (where n is the space available) are also scanned from right to left and segmented as described above. This process continues until the entire label has been printed.

Error Processing

If during compilation, a CELL CONTENTS ERROR error occurs, check that:

• Not more than 1 observation variable or statistic has been nested into the same table cell.

• That there is an observation variable nested with each cell that contains statistics such as mean and median.

• The base marker(s) have been specified for a percentage table.

If there is not enough room on a page to print an entire row or column, TABULATE breaks the wafer into + "chunks". If a BY clause has been specified, all of the rows or columns beneath the highest level in a nesting must fit on a single page, otherwise a CHUNK FORMATTING ERROR is reported. This implies that a wafer is too large to print on a single page and that it cannot be broken at any 'clean' place to produce multiple 'chunks' of output. A wafer can be broken into chunks only along the highest variable nested within the stub or header. The solutions are to:

• Reorganise the TABULATE statement, or

• Change the size formatting options to fit more of each wafer onto each page.