Insert missing strata into a dataset

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Insert missing strata into a dataset

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!

 


Accepted Solutions
Solution
‎04-18-2018 12:52 AM
Super User
Posts: 23,724

Re: Insert missing strata into a dataset

Posted in reply to lymcaliley

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

 

 

View solution in original post


All Replies
Solution
‎04-18-2018 12:52 AM
Super User
Posts: 23,724

Re: Insert missing strata into a dataset

Posted in reply to lymcaliley

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

 

 

New Contributor
Posts: 2

Re: Insert missing strata into a dataset

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 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 86 views
  • 1 like
  • 2 in conversation