Proc Tabulate Formatting for Missing Values

[mary_mcneill]

I have some counties within a Region that do not have any data but I still want that county label to be displayed within the Region variable with zeros for all months. When I add printmiss as an option to the table it will display all counties for the state for each Region, not just the counties within the Region itself. Does anyone know how I can code to only display the counties within the region to display with all zeros for each month if there is no data for that county?

options missing=0;
proc tabulate data=work.data format=5.;
format region $regionfmt. county $cntyfmt. month monthfmt. ;
class region county month year / preloadfmt;
where year = 2021;
table Region*(county all) all,month all;
keylabel n=' ' all='Total';
run;

Below is my output currently with the above code. Region 1 has 8 counties, but County 1 is missing because there is no data for that year. I am trying to still have the county name display with all 0s for each month.

In my SAS code, Region is coded the following way:

format region $10.;
if county in ('County 1','County 2','County 3','County 4','County 5','County 6','County 7','County 8')
then region = 'Region 1';

[sbxkoenk]

TABULATE procedure -- TABLE statement :

• PRINTMISS

prints all values that occur for a class variable each time headings for that variable are printed, even if there are no data for some of the cells that these headings create. Consequently, PRINTMISS creates row and column headings that are the same for all logical pages of the table, within a single BY group.

• MISSTEXT='text'

supplies up to 256 characters of text to be printed for table cells that contain missing values.

• MISSTEXT={<label='text'> <STYLE=style-override(s)>}

supplies up to 256 characters of text to be printed and specifies a style override for table cells that contain missing values. For details about the arguments of the STYLE= option and how it is used, see  STYLE= in the TABLE statement.

table ... ... / printmiss misstext='0';

Ciao, Koen

[mary_mcneill]

I tried using printmiss in the table; however, this does not work because it will display all counties for the state for each Region, but I only want the counties within that region to be displayed, even if there is no data for that particular county within the Region. 

[sbxkoenk]

Okay, probably PRINTMISS option in PROC TABULATE behaves like the SPARSE option in PROC FREQ. The PRINTMISS / SPARSE option(s) provide "all possible combinations of levels of the variables in the input-table, even when some combination levels do not occur in the data".

The PRINTMISS / SPARSE option(s) cannot know that one class variable is hierarchically nested in another one.

Can't you enrich your input dataset with state / region / county records that are absent in your current input dataset? Just put missing values for all other variables. No need to do that "manually" , just find yourself a geo-map dataset (state / region / county) and execute a merge.

Koen

[mary_mcneill]

I am pulling cases for all of Ohio to create a report organized by Region and County for the entire state which is why I have all counties for Ohio in my dataset. I am exploring some alternative coding. I have thought about outputting datasets by Region and then I would be able to use the printmiss since only the counties within that Region would be in the dataset and just use a macro to run the same proc tabulate for each regional dataset, I will just need to see how that will work with my final report. I just wasn't sure if there was a way to group the counties by region like you can do in Proc Report. Thanks for all your help!

[Ksharp]

Did you check my post at this ?

You need option CLASSDATA= .

https://communities.sas.com/t5/SAS-Procedures/Proc-Report-Renaming-the-summary-line-for-break-and-rb...