Customize Output Table to Include Missing Variable

Question

I have an excel file with sheets that contain both the table shells (including formulas) and the raw data output from SAS (eg. Sheet 1 = Table Shell; Sheet 2 = Table 1 Data).

I am trying to update the table data without having to re-do the table shells and corresponding formulas. However, for the time series I have 6 participant groups, but one did not report data this cycle. The problem is that the missing group is sandwiched in the middle, and I can not paste the SAS output to the excel sheets. I want to output that missing group in the results but as blank counts and percents.

Right now I am doing pretty standard proc freq table var*Group/ norow nocol to get the count and total percent.

    Group 1 | Group 2 | ... | Missing Group | Group 5 | Group 6
N    500         303                           475        630
%    

Thanks in advance for any help!

Answer 1

I don't think PROC FREQ can do an entirely missing column, unfortunately. PROC TABULATE can, as can most procedures that have a CLASS variable, using preloadfmt on the class statement and printmiss to tell it to create all logical groupings (which can be dangerous, so use it with caution on complicated tables).

proc format;
value $genderf
'M'='Male'
'F'='Female'
'T'='Trans'
;
quit;
proc tabulate data=sashelp.class;
class sex/preloadfmt;
var height;
format sex $genderf.;
tables sex,height*mean/printmiss;
run;

The only real way to make it work in PROC FREQ is to either postprocess the data (if you're looking for a dataset rather than a printed output), or to add a dummy row for your missing time period, which would of course alter your results.