TABULATE Procedure

Results: TABULATE Procedure

Missing Values

How PROC TABULATE Treats Missing Values

How a missing value for a variable in the input data set affects your output depends on how you use the variable in the PROC TABULATE step. The following table summarizes how the procedure treats missing values.

Summary of How PROC TABULATE Treats Missing Values

Condition	PROC TABULATE Default	To Override Default
An observation contains a missing value for an analysis variable	Excludes that observation from the calculation of statistics (except N and NMISS) for that particular variable	No alternative
An observation contains a missing value for a class variable	Excludes that observation from the table(footnote1)	Use MISSING in the PROC TABULATE statement, or MISSING in the CLASS statement
There are no data for a category	Does not show the category in the table	Use PRINTMISS in the TABLE statement, or use CLASSDATA= in the PROC TABULATE statement
Every observation that contributes to a table cell contains a missing value for an analysis variable	Displays a missing value for any statistics (except N and NMISS) in that cell	Use MISSTEXT= in the TABLE statement
There are no data for a formatted value	Does not display that formatted value in the table	Use PRELOADFMT in the CLASS statement with PRINTMISS in the TABLE statement, or use CLASSDATA= in the PROC TABULATE statement, or add dummy observations to the input data set so that it contains data for each formatted value
A FREQ variable value is missing or is less than 1	Does not use that observation to calculate statistics	No alternative
A WEIGHT variable value is missing or 0	Uses a value of 0	No alternative

This section presents a series of PROC TABULATE steps that illustrate how PROC TABULATE treats missing values. The following program creates the data set and formats that are used in this section and prints the data set. The data set COMPREV contains no missing values. (See the following figure.)

proc format;
   value cntryfmt 1='United States'
                  2='Japan';
   value compfmt  1='Supercomputer'
                  2='Mainframe'
                  3='Midrange'
                  4='Workstation'
                  5='Personal Computer'
                  6='Laptop';
run;

data comprev;
   input Country Computer Rev90 Rev91 Rev92;
   datalines;
1 1 788.8 877.6 944.9
1 2 12538.1 9855.6 8527.9
1 3 9815.8 6340.3 8680.3
1 4 3147.2 3474.1 3722.4
1 5 18660.9 18428.0 23531.1
2 1 469.9 495.6 448.4
2 2 5697.6 6242.4 5382.3
2 3 5392.1 5668.3 4845.9
2 4 1511.6 1875.5 1924.5
2 5 4746.0 4600.8 4363.7
;

proc print data=comprev noobs;
   format country cntryfmt. computer compfmt.;
   title 'The Data Set COMPREV';
run;

The Data Set COMPREV

No Missing Values

The following PROC TABULATE step produces the following figure:

proc tabulate data=comprev;
   class country computer;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32;
   format country cntryfmt. computer compfmt.;
   title 'Revenues from Computer Sales';
   title2 'for 1990 to 1992';
run;

Because the data set contains no missing values, the table includes all observations. All headings and cells contain nonmissing values.

Computer Sales Data: No Missing Values

Revenue from Computer Sales for 1990 to 1992

A Missing Class Variable

The next program copies COMPREV and alters the data so that the eighth observation has a missing value for Computer. Except for specifying this new data set, the program that produces Computer Sales Data: Midrange, Japan, Deleted is the same as the program that produces Computer Sales Data: No Missing Values. By default, PROC TABULATE ignores observations with missing values for a class variable.

data compmiss;
   set comprev;
   if _n_=8 then computer=.;
run;

proc tabulate data=compmiss;
   class country computer;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32;
   format country cntryfmt. computer compfmt.;
   title 'Revenues from Computer Sales';
   title2 'for 1990 to 1992';
run;

The observation with a missing value for Computer was the category

Midrange,
                           Japan

. This category no longer exists. By default, PROC TABULATE ignores observations with missing values for a class variable, so this table contains one less row than Computer Sales Data: No Missing Values.

Computer Sales Data: Midrange, Japan, Deleted

Including Observations with Missing Class Variables

This program adds the MISSING option to the previous program. MISSING is available either in the PROC TABULATE statement or in the CLASS statement. If you want MISSING to apply only to selected class variables, but not to others, then specify MISSING in a separate CLASS statement with the selected variables. The MISSING option includes observations with missing values of a class variable in the report. (See the following figure.)

proc tabulate data=compmiss missing;
   class country computer;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32;
   format country cntryfmt. computer compfmt.;
   title 'Revenues from Computer Sales';
   title2 'for 1990 to 1992';
run;

This table includes a category with missing values of Computer. This category makes up the first row of data in the table.

Computer Sales Data: Missing Values for Computer

Formatting Headings for Observations with Missing Class Variables

By default, as shown in Computer Sales Data: Missing Values for Computer, PROC TABULATE displays missing values of a class variable as one of the standard SAS characters for missing values (a period, a blank, an underscore, or one of the letters A through Z). If you want to display something else instead, then you must assign a format to the class variable that has missing values, as shown in the following program. (See the following figure.)

proc format;
   value misscomp 1='Supercomputer'
                  2='Mainframe'
                  3='Midrange'
                  4='Workstation'
                  5='Personal Computer'
                  6='Laptop'
                  .='No type given';
run;

proc tabulate data=compmiss missing;
   class country computer;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32;
   format country cntryfmt. computer misscomp.;
   title 'Revenues for Computer Sales';
   title2 'for 1990 to 1992';
run;

In this table, the missing value appears as the text that the MISSCOMP. format specifies.

Computer Sales Data: Text Supplied for Missing Computer Value

Revenues for Computer Sales for 1990 to 1992

Providing Headings for All Categories

By default, PROC TABULATE evaluates each page that it prints and omits columns and rows for categories that do not exist. For example, Computer Sales Data: Text Supplied for Missing Computer Value does not include a row for No type given and for

United
                                 States

or for Midrange and for Japan because there are no data in these categories. If you want the table to represent all possible categories, then use the PRINTMISS option in the TABLE statement, as shown in the following program. (See the following figure.)

proc tabulate data=compmiss missing;
   class country computer;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32 printmiss;
   format country cntryfmt. computer misscomp.;
   title 'Revenues for Computer Sales';
   title2 'for 1990 to 1992';
run;

This table contains a row for the category No type given,

United
                              States

and the category Midrange, Japan. Because there are no data in these categories, the values for the statistics are all missing.

Computer Sales Data: Missing Statistics Values

Providing Text for Cells That Contain Missing Values

If some observations in a category contain missing values for analysis variables, then PROC TABULATE does not use those observations to calculate statistics (except N and NMISS). However, if each observation in a category contains a missing value, then PROC TABULATE displays a missing value for the value of the statistic. To replace missing values for analysis variables with text, use the MISSTEXT= option in the TABLE statement to specify the text to use, as shown in the following program. (See the following figure.)

proc tabulate data=compmiss missing;
   class country computer;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32 printmiss misstext='NO DATA!';
   format country cntryfmt. computer misscomp.;
   title 'Revenues for Computer Sales';
   title2 'for 1990 to 1992';
run;

This table replaces the period normally used to display missing values with the text of the MISSTEXT= option.

Computer Sales Data: Text Supplied for Missing Statistics Values

Providing Headings for All Values of a Format

PROC TABULATE prints headings only for values that appear in the input data set. For example, the format COMPFMT. provides for six possible values of Computer. Only five of these values occur in the data set COMPREV. The data set contains no data for laptop computers.

If you want to include headings for all possible values of Computer (perhaps to make it easier to compare the output with tables that are created later when you do have data for laptops), then you have three different ways to create such a table:

Use the PRELOADFMT option in the CLASS statement with the PRINTMISS option in the TABLE statement. See Using Preloaded Formats with Class Variables for another example that uses PRELOADFMT.
Use the CLASSDATA= option in the PROC TABULATE statement. See Specifying Class Variable Combinations to Appear in a Table for an example that uses the CLASSDATA= option.
Add dummy values to the input data set so that each value that the format handles appears at least once in the data set.

The following program adds the PRELOADFMT option to a CLASS statement that contains the relevant variable.

The results are shown in the following figure.

proc tabulate data=compmiss missing;
   class country;
   class computer / preloadfmt;
   var rev90 rev91 rev92;
   table computer*country,rev90 rev91 rev92 /
         rts=32 printmiss misstext='NO DATA!';
   format country cntryfmt. computer compfmt.;
   title 'Revenues for Computer Sales';
   title2 'for 1990 to 1992';
run;

This table contains a heading for each possible value of Computer.

Computer Sales Data: All Possible Computer Values Included

Understanding the Order of Headings with ORDER=DATA

The ORDER= option applies to all class variables. Occasionally, you want to order the headings for different variables differently. One method for reordering the headings is to group the data as you want them to appear and to specify ORDER=DATA.

For this technique to work, the first value of the first class variable must occur in the data with all possible values of all the other class variables. If this criterion is not met, then the order of the headings might surprise you.

The following program creates a simple data set in which the observations are ordered first by the values of Animal, then by the values of Food. The ORDER= option in the PROC TABULATE statement orders the heading for the class variables by the order of their appearance in the data set. (See the following figure.) Although bones is the first value for Food in the group of observations where Animal=dog, all other values for Food appear before bones in the data set because bones never appears when Animal=cat. Therefore, the heading for bones in the table in the following figure is not in alphabetical order.

In other words, PROC TABULATE maintains for subsequent categories the order that was established by earlier categories. If you want to re-establish the order of Food for each value of Animal, then use BY-group processing. PROC TABULATE creates a separate table for each BY group, so that the ordering can differ from one BY group to the next.

data foodpref;
   input Animal $ Food $;
   datalines;
cat fish
cat meat
cat milk
dog bones
dog fish
dog meat
;

proc tabulate data=foodpref format=9.
              order=data;
   class animal food;
   table animal*food;
run;

Ordering the Headings of Class Variables

Portability of ODS Output with PROC TABULATE

Under certain circumstances, using PROC TABULATE with the Output Delivery System produces files that are not portable. If the SAS system option FORMCHAR= in your SAS session uses nonstandard line-drawing characters, then the output might include strange characters instead of lines in operating environments in which the SAS Monospace font is not installed. To avoid this problem, specify the following OPTIONS statement before executing PROC TABULATE:

options formchar="|----|+|---+=|-/\<>*";

FOOTNOTE 1: The CLASS statement applies to all TABLE statements in a PROC TABULATE step. Therefore, if you define a variable as a class variable, PROC TABULATE omits observations that have missing values for that variable even if you do not use the variable in a TABLE statement.[return]