Tabulate

TABULATE
     FILENAME = { filename } /
     expressions /

Example: BY

SEX BY AGE BY INCOME

Example: THEN

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.

Example: BY and THEN

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. (AGE THEN INCOME) BY SEX

Other combinations might be: AGE BY INCOME THEN SEX

AGE BY (INCOME THEN SEX)

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

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

REGION,SALARY BY AGE BY SEX

AGE, SEX THEN (SALARY BY SEX)

REGION,AGE BY (SEX THEN SALARY)

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

.CONTROL   (.C)
.OBS       (.O)
.FIRST     (.F)
.TOTAL     (.T)
.MISSING   (.M)
.UNDEFINED (.U)
.SPAN      (.S)
.NOSPAN    (.NOS)

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.

When a control variable has a missing or undefined value, it is normally excluded from the detail cells in the table. Specify the .MISSING or .UNDEFINED modifiers to generate additional cells in the table. The .UNDEFINED modifier groups all missing and undefined values into a single cell. The .MISSING modifier displays undefined and each defined missing value as separate cells. These modifiers override any DELETEMISSING specification for that variable.

Example: Undefined Modifier

By default, the rows and columns of a table are labeled with value labels (if they exist) or with the "variable=value" notation. The .SPAN modifier generates a second level of heading for a variable. This heading is either the variable label (if it exists) or the variable name. This new heading "spans" all the categories of the variable, hence the name .SPAN.

Example: SPAN Modifier

REGION,AGE BY SEX

REGION.S,AGE.S BY SEX.S

The SPANNERS option can be used to turn on spanning labels for all variables. The .NOSPAN modifier turns off spanning labels for a particular variable when the SPANNERS option is in effect.

Example: 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.)

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

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  SALARY BY AGE BY (TOTAL THEN SEX) /
TABULATE  SALARY BY AGE BY SEX.T /

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  REGION.T, SALARY BY (MEAN THEN Q) /
         QUANTILES = Q 'Quantiles' 4 /

A specification in the first form , with multiple labels:

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

A specification in the second form:

TABULATE  REGION.T, SALARY BY (MEAN THEN D) /
  QUANTILES = D 'Deciles' (10) '10th' (50) '50th' (90) '90th'/

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.

To display percentages, specify where the percents appear and the base of each percent calculation. Use the PERCENT option to define a pseudo-variable and then reference this in an expression to display the percent. Specify the base variable to use to calculate the percentage by appending a percentage symbol (%) and the same pseudo-variable name to a base variable. The only valid base variables are TOTAL control variables, typically cross tabulated against the observation variable that is the percentage variable.

Shorthand Notation for Base Marker Specification

TOTAL%X THEN REGION
REGION%X

TABULATE  X BY (TOTAL%X THEN AGE), TOTAL%X THEN REGION /
TABULATE X BY AGE%X, REGION%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.

TOTAL THEN AGE,TOTAL THEN REGION

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   TOTAL THEN AGE, X BY (TOTAL%X THEN REGION) /
         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   X BY (TOTAL THEN AGE), TOTAL%X THEN REGION /
         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   TOTAL%X THEN AGE, X BY (TOTAL THEN REGION) /
         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  TOTAL%X THEN AGE, X BY (TOTAL%X THEN REGION) /
          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.

Example: Percentages for Observation Variables

TABULATE  SALARY BY (TOTAL THEN AGE) /

The corresponding percentage table is:

TABULATE  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  (TOTAL%X THEN AGE) BY X BY SALARY /
         PERCENT = X 'Percent' /

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

TABULATE  AGE.T, REGION%X BY X.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  AGE.T, X.T BY REGION%X /
         PERCENT = X 'Pct' /

TABULATE  AGE.T, TOTAL THEN (X BY REGION%X) /
         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  AGE.T, SALARY BY SEX%X BY X.T /
         PERCENT = X 'Pct' /

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

TABULATE  AGE.T, SALARY BY X.T BY SEX%X /
         PERCENT = X 'Pct' /

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

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

TABULATE  X.T, AGE%X, REGION%X /
         PERCENT = X 'Percent' /

X.T,AGE%X,REGION%X
Table of Contents:
TOTAL........................................  page 1
Percent......................................  page 2

X.T,AGE%X,REGION%X
TOTAL..............

(On next page)

X.T,AGE%X,REGION%X
Percent............

TABULATE  AGE.T BY SEX.T, REGION.T

Base marker appended to AGE

TABULATE  AGE%X BY SEX.T, X BY REGION.T/ PERCENT = X 'Percent'

Base marker appended to SEX

TABULATE  AGE BY SEX%X, X BY REGION.T / PERCENT = X 'Percent'

Use of FIRST

TABULATE  AGE BY SEX%X.F, X BY REGION.T /
         PERCENT = X 'Percent'

Multiple Base Markers

TABULATE  AGE%X BY SEX.T, X BY REGION%X /
         PERCENT = X 'Percent'

TABULATE  AGE BY SEX%X, X BY REGION%X /
         PERCENT = X 'Percent'

TABULATE  AGE BY SEX%X.F, X BY REGION%X /
         PERCENT = X 'Percent'

Multiple Percent Variables Within a Table

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

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

Multiple percents in Rows

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

Example: Overall Percents

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

Counts and Multiple Percents

TABULATE  AGE%PC%PT BY (TOTAL + PR + PC + PT), REGION%PR%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 GENDER.T, AGEGROUP.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 TOTX%X THEN GENDER, TOTY THEN AGEGROUP THEN X/
FILENAME = A /
TOTAL = TOTX 'Total Males and Females' /
PERCENT = X '% BY AGE' /
TABULATE Y, TOTY%Y THEN AGEGROUP /
TOTAL = TOTY 'Total Young and Old' /
PERCENT = Y '% BY GENDER'  /

BOOLEAN  = (logical expression)
SAMPLE   = fraction [,seed ]
WEIGHT   = varname

RACE=1   REGION=2   FAMSIZE =5

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.

AGE.T , SALARY BY (TOTAL THEN (X BY SEX%X)) /
         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 SALARY BY REGION BY (MIN THEN MAX) /
         MINIMUM = MIN 'Lowest' /
         MAXIMUM = MAX 'Highest' /
         PRINTFORMATS = SALARY (D) MIN (2)MAX (2) /

TABULATE 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.

Labels

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

When not enough room is provided in a table for label or title information, the label or title is broken into two or more segments so that it can fit in the allotted space. The VALUE LABELS command defines value labels for missing values including BLANK and UNDEFINED.

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/DBMS 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.

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.

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

STUBTITLE	Specifies a title which appears in the boxed in area to the left of the header and directly above the stub labels. The options allowed are CENTER, LEFT and RIGHT and control the justification of the stub label. If the option is omitted, it is assumed to be CENTER. If the stub title is too long, it is automatically segmented over two or more lines. There is no default stub title.
STUBWIDTH	Specifies the number of print positions for stub labels. The default is 20 print positions. If "too much" stub label segmentation occurs, TABULATE aborts with a WAFER/STUB OUTPUT FORMATTING ERROR. The solution is to increase the STUBWIDTH.
STUBINDENTATION	Specifies the indentation for each level of nesting. The default is 2 print positions.
STUBCONTINUATION	Specifies the indentation for continuation lines. A label which exceeds the stub width is segmented into two or more lines. Continuation lines are indented by the parameter. The default indentation is 3 print positions.
STUBHEIGHT	Specifies the number of print lines used for each stub label. The default is 1 line. Label segmentation works independently of the STUBHEIGHT parameter and, therefore, some stub labels may occupy more than STUBHEIGHT lines.
STUBFILLER	Stub labels (on lines which contain data) are filled with the character to the full width of the stub. The default stub filler is a period (.).
STUBDIVIDER	Specifies the horizontal divider character. The default is the dash (-).

Stub Formatting

TABULATE  AGE BY SEX, REGION.T /
         STUBTITLE = 'Age by Sex' /
         STUBWIDTH = 20

TABULATE  AGE BY SEX, REGION.T /
         STUBFILLER = ' '

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

Wafer labels are formed in the same way that header and stub labels are produced.

WAFERTITLE	Specifies a wafer title printed above each wafer. By default, the tabulate statement is printed as the wafer title, centred and one line above the wafer (table). The option may be CENTER, LEFT or RIGHT to position the wafer title over the wafer.
NOWAFERTITLE	Suppresses printing of the wafer title.
WAFERCENTER	Centres the wafer on the page. By default wafers are left justifies on the page.
WAFERSTART	Specifies the starting print position for the wafer. Does not apply when WAFERCENTER is specified.
WAFERINDENTATION	Specifies the indentation used for each level of nesting in the wafer title. The default is 2 print positions.
WAFERCONTINUATION	Specifies the indentation for continuation lines when a wafer label is broken up into multiple lines. The default is 3 print positions.
WAFERHEIGHT	Specifies the number of print lines for the wafer label. The default is 1. This height is automatically adjusted if a label does not fit in the width of the wafer title and has to be segmented.

The number of print positions available for printing the wafer label is the same as the width of the stub titles. If the wafer title does not fit, TABULATE reports a WAFER/STUB OUTPUT FORMATTING ERROR. The solution is to increase the size of STUBWIDTH.

Wafers

TABULATE  AGE BY REGION, SEX, RACE
         Under 21
          North.......

         Under 21
          South.......

         21 and Over
           North.......

         21 and Over
           South.......

HTML 
NOBOTTOMBORDER 
NOLEFTBORDER 
NORIGHTBORDER 
NOTES     = 'string'
FOOTNOTES = 'string' 
WAFERNOTES = 'string' 
NOZEROS 
ZEROCHARACTER = 'character' 
NOEMPTYROWS 
DELETEMISSING 
UPPERCASE 
SPANNERS 
DUMMY   = variable 'label' ...
LANGUAGE 

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.

Border Generation

Footnotes

Zero Printing

Suppression of Empty Rows and Wafers

Exclusion of Records with Missing Values

Converting Text to Uppercase

Spanner Labels

Dummy Spanner Labels

Example: Dummy Spanner Labels

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

Language

String Specifications

'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.

Label Segmentation

• 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.

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.