Force all values of categorical variable to appear in proc tabulate with missing data

[tipscode]

In SAS 9.4 I have a dataset where there are US states with missing data for all variables and would like to have those states to still show up in proc tabulate.

I was considering making an empty dataset with simply all 52 states, along with a dummy variable default to 0, and merge this dataset with the existing dataset to do this.

Thank you in advance for any suggestions.

[ballardw]

Generally with Proc Tabulate the approach for this is to use the option Preloadfmt for the class variable. This does require that you have a format for the variable with all of the levels you want displayed. Then add the Printmiss option to the table options or use order=data for the class variable.

Here is a very trivial example:

proc format library=work;
value $a
'A' = 'Apple'
'B' = 'Banana'
'C' = 'Cat'
'D' = 'Dog'
;
run;

data tab;
   do grp = 'A','C','D';
     i+1;
     output;
   end;
run;


proc tabulate data= tab;
   class grp/ preloadfmt;
   format grp $a.;
   var i;
   table grp,
         i *(n mean)
         / printmiss;
run;

Note that if you use a multilabel format then the order of statements in the format definition and the options order= will seriously effect the appearance.

[tipscode]

Thank you.

In the end, I created a format for the 52 US states and applied them to the proc tabulate like so:

 PROC TABULATE NOSEPS MISSING FORMAT=7. DATA=tbls;
 CLASS STATEFIP /PRELOADFMT;
 CLASS SERVICES YR;
 TABLES YR, STATEFIP, SERVICES / PRINTMISS MISSTEXT='0';
 WHERE YR = &SAMPLEYR1;
 FORMAT SERVICES SVC. STATEFIP $STATE.;
 RUN;