Proc tabulate: How can I include missing data in counts but not print the row or column of the missing value

[saul_kanowitz_cdph_ca_gov]

I am runing proc tabulate using ODS to output to xml files for use in Excel.

I am trying to count records with unknown values in the calculation of percents, but I don't want to print the unknown data (highlighted in red) in my proc tabulate output.

For ease of reference, I have numbered the different columns 1 to 12.

In the snippet of the output from proc tabulate, I need to count the 6 unknown records in column 4 in the total count in column 2, but I don't want to print columns 4 through 6.

Basically I want to count in my total cases (column 2) the missing data (column 4) for the column variable, "Prior Disease" (columns 4,7,10), but I don't want to display the columns (4-6) associated with the missing values for the "PRIOR Disease" variable.

If I don't use the missing option in proc tabulate, then the "Unknown Disease" (columns 6-8) don't print, which is what I want, but my totals in columns 2 and 3 goes down by 6, which I don't want to have happen.

If I use the missing option in proc tabulate, then the "Unknown Disease" (columns 6-8) print, which I don't want, but my overall totals in column 2 and 3 are counted correctly, which I want.

My code is below the table snippet.

1	2	3	4	5	6	7	8	9	10	11	12	13	14
Reporting Jurisdiction	Total Cases	Total Tested	Positive		Unknown Disease			No Prior Disease			Prior Disease
					Tested	Positive		Tested	Positive		Tested	Positive
			No.	%		No.	%		No.	%		No.	%
State	1719	1608	158	9.8	6	0	0.0	1524	145	9.5	78	13	16.7

Below is my proc tabulate code:

proc tabulate data=table47 format=4.1 missing;

class jurisdic prevdisease;

var inh suscdone cultpos;

table all={label="State"} jurisdic,

cultpos={label="Total Cases"} suscdone={label="Total Tested"}

inh={label="Positive"}*(n pctn<suscdone>*[s=[tagattr='format:##0.0']])

prevdisease={label=""}*(suscdone={label="Tested"} inh={label="Positive"}*(n pctn<suscdone>*[s=[tagattr='format:##0.0']]))/

box={label='Reporting Jurisdiction' s=[just=l]} indent=4 ;

keylabel n='No.';

keylabel sum=' ';

keylabel PctN='%';

format prevdisease $t47prevdisease.;

run;

Thanks,

Saul Kanowitz

[ballardw]

This may be a case of needing to presummarize the data prior to display. To include var values associated with a class variable the class variable value must appear in the output - missing as it were with that option set.

I would be tempted to try the tabulate code (without style overrides as not needed) to create an output data set.

Then use that as input to a modified code to display the values but drop the records where that variable is missing.