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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.