05-23-2017 08:52 AM
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.
05-23-2017 10:12 AM - edited 05-23-2017 01:59 PM
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.
05-24-2017 03:38 PM - edited 05-24-2017 03:52 PM
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;