The SAS Output Delivery System and reporting techniques

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

Reply
Occasional Contributor
Posts: 6

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

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.

1234567891011121314
Reporting JurisdictionTotal CasesTotal TestedPositiveUnknown DiseaseNo Prior DiseasePrior Disease
TestedPositiveTestedPositiveTestedPositive
No.%No.%No.%No.%
State171916081589.8600.015241459.5781316.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

Super User
Posts: 11,343

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

Posted in reply to saul_kanowitz_cdph_ca_gov

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.

Ask a Question
Discussion stats
  • 1 reply
  • 848 views
  • 0 likes
  • 2 in conversation