Proc tabulate: show subtotals by variable and suppress missing values

[lml819]

I am using proc tabulate to show demographic characteristics of a client population over several years. There are some missing values, and my understanding is that proc tabulate excludes the entire observation if a value for any variable in the class statement is missing. I want to keep all observations and show subtotals by demographic characteristic, without having a "Missing" or "Unknown" category for each group. 

Here is what I have:

Here is what I want:

Simplified version of code (I have several more class variables in the actual dataset):

proc tabulate data=all;
class raceeth gender year/missing;
table (raceeth all='Subtotal')(gender all='Subtotal'), year*(N='No.' colpctn='%'*f=5.1);
run;

Is this possible in proc tabulate, or do I need to use a different method? 

[Astounding]

Instead of printing the table, put the output into a data set.  Do not bother with subsets or percentages, just keep the original counts.  If it's easier for you, just use PROC SUMMARY to get the counts into an output data set.

Then use a DATA step to remove the missing categories from that output data set.

Then feed the remaining subset with no missing values back into a new PROC TABULATE to print your table.  Since you already have totals, you may need to switch to the SUM statistic (and pctSUM) instead of N (and pctN).

You are 100% correct about PROC TABULATE removing observations where even one class variable has a missing value.

[lml819]

Thank you for the suggestion! I'll try creating an output dataset.

[Ksharp]

" I want to keep all observations and show subtotals"

I don't understand. If you want to count all the obs (include missing value) and don't want display mssing value in report ,then you need other proc like PROC SQL to customize your report.

Otherwise, you could get rid of missing value from your dataset.

[lml819]

Yes, I want to keep all observations. If Client A is missing race but has gender, I still want to count their gender. But when I print the table, I don't want a "missing" category to show under race. I agree this might not make sense, but it's what I was asked to create ¯\_(ツ)_/¯  If proc tabulate won't work, I'll try proc sql to restructure the data.

[Ksharp]

Yeah. If you don't want to print MISSING value, try PROC SQL which is most convenient and useful tool to build a report.

[Ksharp · Accepted solution]

OK. If I understand what you mean.

data have;
 set sashelp.heart(obs=200);
run;

proc sql;
create table report as
select 'DeathCause' as a,' '||DeathCause as b,sex as c,
 count(*) as _n label='No.',
 100*calculated _n/(select count(*) from have where Sex=a.Sex) as p format=8.2 label='%'
 from have as a
  where DeathCause is not missing
   group by DeathCause,Sex
union all
select 'DeathCause' ,'SubTotal',sex,
 count(*) as _n label='No.',
 100*calculated _n/(select count(*) from have where Sex=a.Sex) as p format=8.2 label='%'
 from have as a
  where sex is not missing 
   group by sex

union all

select 'bp_status' as a,' '||bp_status as b,sex as c,
 count(*) as _n label='No.',
 100*calculated _n/(select count(*) from have where Sex=a.Sex) as p format=8.2 label='%'
 from have as a
  where bp_status is not missing
   group by bp_status,Sex
union all
select 'bp_status' ,'SubTotal',sex,
 count(*) as _n label='No.',
 100*calculated _n/(select count(*) from have where Sex=a.Sex) as p format=8.2 label='%'
 from have as a
  where sex is not missing
   group by sex
;
quit;



proc report data=report nowd;
columns a b c,(_n p);
define a/group noprint;
define b/group '';
define c/across '';
compute before a/style={just=l};
line a $40.;
endcomp;
run;

[lml819]

This is it! Thank you, I appreciate your time 🙂 

[Patrick]

@lml819 You could just exclude the missings from the source data using one of below two options.

What you can't do with Proc Tabulate is to then include the missings for the last total line. That's imho also really hard to understand and somewhat illogical.