Force proc tabulate to output all columns for tables even when there is no data

[AlexHarrison]

Hi all, apologies if this question has been dealt with before - I'm sure it will have been but I have done some extensive searching and I've not been able to figure out the specific search terms to get the answer I need!

I'm trying to regularly produce a series of output tables in SAS E Guide 8.3, that look at a range of characteristics of our population based on their sex, using data for different years. I'll use an example characteristic of marital status where the response options are Married/Single/Widowed/Divorced. Here is the code I would use to produce the outputs for each year:

proc tabulate data=data;
where year = &y1.; 
class sex marital_status;
table sex= '', marital_status=''*rowpctn='';
run;

The above would of course be repeated for each year of interest via the where variable.

The issue arises because, in certain years, none of our population have selected one of the response options, for instance "Widowed" in year one. As a result (as seen below), in that year, our output table has only three columns of data as opposed to the four columns in other years. This is a problem because I need to do some calculations (eg three year averages) on these outputs in an excel spreadsheet, and due to the fact that I am looking at a whole range of characteristics where this issue applies (lots and lots of output tables) I would ideally like to have the spreadsheets set up to automatically update when new data is input. The spreadsheet cannot automate properly if we get tables of inconsistent width, where the position of the variables can change depending on what is omitted. 

Basically I want to know how I can 'force' SAS to output the tables like below, where in any year where there are no observations for a variable option, we still get that column in the table, albeit displaying 0s. This would allow me to fully automate our tables and save me a lot of time. I'm assuming there's a way of doing this with macros that I am so far unfamiliar with? 

Thanks in advance!

[Patrick]

Change your Proc Tabulate to something like below.

• One call of the Proc for all years
• Use of a Classdata set to ensure it also prints years for which you don't have any data.

proc tabulate data=data classdata=...;
class year sex marital_status;
table year, sex= '', marital_status=''*rowpctn='';
run;

PROC TABULATE Statement

CLASSDATA=SAS-data-set

[ballardw · Accepted solution]

Another bit that may be applicable is a custom format for the variable and the PRELOADFMT option.

Example, note the SASHELP.CLASS data set has no values of X in the data.

proc format ;
value $sexcat
'F' = 'Female'
'M' = 'Male'
'X' = 'Other';
run;

proc tabulate data=sashelp.class;
  class sex /preloadfmt;
  format sex $sexcat.;
  class age;
 table age,
       sex*n
       /printmiss misstext='0';
run;

The PRELOADFMT option has some other syntax requirements depending on what you want for output so do read the documentation.

I would also say I would be strongly tempted to do any calculation in SAS as I get very nervous with Excel "automation" relying on positions of values. For example your three-year average could be counting 0 values (as shown in your forced output) as valid counts and getting invalid averages. Second, your code shows a ROWPCTN statistic requested. Unless the overall N is exact for each year then "averaging" of percentages is going to be just plain wrong.

[Ksharp]

https://communities.sas.com/t5/SAS-Programming/Want-to-merge-datasets-with-0-observations-with-the-h...
https://communities.sas.com/t5/SAS-Programming/Creating-a-variable-that-combines-same-levels-of-exis...