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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.