Insert missing strata into a dataset

lymcaliley — Tue, 17 Apr 2018 16:08:15 GMT

Hello,

I'm using SAS 9.4. I have a dataset of counts of mortality for about 1000 census tracts for a particular area. These counts are stratified on five age categories, three racial categories, sex and ethnicity (Hispanic vs not Hispanic). If there are any deaths in a given strata in a census tract within my time period of interest, the strata is listed. If 1-4 deaths occurred in that strata, the count is censored, and the count is marked as missing. If zero deaths occurred, the strata is not listed at all. I would like to insert rows for the absent "zero count" strata. The attached file is my full dataset. I have a simplified version of my dataset with just a few census tracts below:

data mort;
input GEOID_data SEX RACE LIFESTAGE ETHNICITY Deaths;
datalines;
13013180103 1 1 5 1 .
13013180103 1 1 5 0 .
13013180103 1 1 6 0 9
13013180103 2 1 6 0 .
13013180103 2 3 6 0 .
13013180103 1 1 7 0 14
13013180103 2 1 7 0 8
13013180103 1 2 7 0 .
13013180103 2 2 7 0 .
13013180103 2 1 7 1 .
13013180103 1 1 8 0 23
13013180103 1 1 8 0 11
13013180103 2 1 8 0 10
13013180103 2 1 8 0 5
13013180103 1 2 8 0 .
13013180103 2 2 8 0 .
13013180103 2 1 9 0 47
13013180103 1 1 9 0 30
13013180103 1 2 9 0 .
13013180103 1 1 9 1 .
13013180104 1 1 5 0 .
13013180104 2 1 5 0 .
13013180104 2 1 6 0 .
13013180104 1 1 6 0 .
13013180104 2 2 6 0 .
13013180104 1 1 7 0 8
13013180104 2 1 7 0 .
13013180104 1 1 8 0 13
13013180104 2 1 8 0 6
13013180104 1 1 8 0 5
13013180104 2 1 8 0 .
13013180104 1 1 9 0 11
13013180104 2 1 9 0 10
13013180104 2 1 9 1 .
13013180104 1 1 9 1 .
13013180105 2 1 5 0 .
13013180105 1 1 5 0 .
13013180105 1 1 6 0 .
13013180105 2 1 6 0 .
13013180105 2 1 7 0 10
13013180105 1 1 7 0 7
13013180105 1 3 7 0 .
13013180105 1 1 8 0 11
13013180105 2 1 8 0 6
13013180105 2 1 8 0 5
13013180105 1 1 8 0 5
13013180105 1 1 9 0 22
13013180105 2 1 9 0 12
13013180105 2 2 9 0 .
;
run;

I would like every census tract to have all 60 possible combinations of lifestage, race, ethnicity and sex listed and to have strata that aren't currently listed have deaths=0. How can I accomplish this?

Thanks!

Re: Insert missing strata into a dataset

Reeza — Tue, 17 Apr 2018 16:13:07 GMT

There are quite a few ways and quite a few papers on this.

If you check here, you'll find several solutions that are applicable to your question:

https://lexjansen.com/search/searchresults.php?q=including%20missing%20categories

Re: Insert missing strata into a dataset

lymcaliley — Wed, 18 Apr 2018 04:52:15 GMT

Thanks for the link! Problem solved. For any others who may be doing the same thing I found this resource particularly helpful: http://support.sas.com/resources/papers/proceedings10/218-2010.pdf

topic Re: Insert missing strata into a dataset in SAS Programming

Insert missing strata into a dataset

Re: Insert missing strata into a dataset

Re: Insert missing strata into a dataset