Counting and categorizing the number of adults in the household using survey data

[Dubile Boko]

Good day,

I am interested in counting and categorizing the number of adults in the household into the following categories:
0=None;
1=One adult;
2=Two adults (Male head & Female spouse);
3=Two adults (Male head & Male spouse or Female head & Female spouse);
4=Three or more related adults;
5=Unrelated adults.

In essence, I want to derive a variable similar to relation_structure. In my case, an adult is any person over the age of 17 years (i.e. 18+ years). Spouse is wife or husband.

Here is an example of the data I'm using.

***********************************************

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 hhid float(strata psu hhsize pid) byte(hhtag line_number relation_structure rel_to_head) float relationship_derived byte(Age Sex marital_status) float person_wgt
"       1  11" 18 1  1 1 1 1 1  1 1 45 1 0 1.269316
"       1  13" 18 1  2 1 1 1 2  1 1 44 1 1 1.269316
"       1  13" 18 1  2 2 0 2 2  2 2 41 2 1 1.269316
"       1  19" 18 1  1 1 1 1 1  1 1 60 1 1 1.269316
"       1  27" 18 1  1 1 1 1 1  1 1 30 1 1 1.269316
"       1  28" 18 1  5 1 1 1 4  1 1 43 1 1 1.269316
"       1  28" 18 1  5 2 0 2 4  2 2 41 2 1 1.269316
"       1  28" 18 1  5 3 0 3 4  3 3 15 2 0 1.269316
"       1  28" 18 1  5 4 0 4 4  3 3  4 2 . 1.269316
"       1  28" 18 1  5 5 0 5 4  3 3  4 2 . 1.269316
"       1  31" 18 1  4 1 1 1 4  1 1 55 1 1 1.269316
"       1  31" 18 1  4 2 0 2 4  2 2 52 2 1 1.269316
"       1  31" 18 1  4 3 0 3 4  3 3 31 1 0 1.269316
"       1  31" 18 1  4 4 0 4 4  3 3 18 1 0 1.269316
"       1  36" 18 1  2 1 1 1 2  1 1 31 1 1 1.269316
"       1  36" 18 1  2 2 0 2 2  2 2 27 2 1 1.269316
"       1  57" 18 1  2 1 1 1 3  1 1 47 1 1 1.269316
"       1  57" 18 1  2 2 0 2 3  3 3 21 1 0 1.269316
"       1  58" 18 1  2 1 1 1 3  1 1 28 1 0 1.269316
"       1  58" 18 1  2 2 0 2 3  8 4 24 1 0 1.269316
"       1  69" 18 1  2 1 1 1 2  1 1 33 1 1 1.269316
"       1  69" 18 1  2 2 0 2 2  2 2 32 2 1 1.269316
"       1  91" 18 1  2 1 1 1 2  1 1 39 1 1 1.269316
"       1  91" 18 1  2 2 0 2 2  2 2 30 2 1 1.269316
"       1  92" 18 1  1 1 1 1 1  1 1 41 1 1 1.269316
"       1  93" 18 1  2 1 1 1 2  1 1 36 1 0 1.269316
"       1  93" 18 1  2 2 0 2 2  8 4 22 2 0 1.269316
"       1  96" 18 1  1 1 1 1 1  1 1 40 1 4 1.269316
"       2   4" 13 2  6 1 1 1 4  1 1 64 2 0 1.287462
"       2   4" 13 2  6 2 0 2 4  3 3 48 1 0 1.287462
"       2   4" 13 2  6 3 0 3 4  3 3 38 2 0 1.287462
"       2   4" 13 2  6 4 0 4 4  3 3 28 1 0 1.287462
"       2   4" 13 2  6 5 0 5 4  3 3 25 1 0 1.287462
"       2   4" 13 2  6 6 0 6 4  5 7  9 1 . 1.287462
"       2  22" 13 2  4 1 1 1 4  1 1 75 2 1 1.287462
"       2  22" 13 2  4 2 0 2 4  3 3 47 1 0 1.287462
"       2  22" 13 2  4 3 0 3 4  5 7 24 2 0 1.287462
"       2  22" 13 2  4 4 0 4 4  5 7 10 2 . 1.287462
"       2  29" 13 2  6 1 1 1 4  1 1 77 2 3 1.287462
"       2  29" 13 2  6 2 0 2 4  3 3 45 2 3 1.287462
"       2  29" 13 2  6 3 0 3 4  4 8 36 2 0 1.287462
"       2  29" 13 2  6 4 0 4 4  5 7 15 1 0 1.287462
"       2  29" 13 2  6 5 0 5 4  5 7  9 1 . 1.287462
"       2  29" 13 2  6 6 0 6 4  5 7  6 2 . 1.287462
"       2  34" 13 2  8 1 1 1 4  1 1 60 2 3 1.287462
"       2  34" 13 2  8 2 0 2 4  5 7 20 1 0 1.287462
"       2  34" 13 2  8 3 0 3 4  5 7  2 2 . 1.287462
"       2  34" 13 2  8 4 0 4 4  4 8 26 2 1 1.287462
"       2  34" 13 2  8 5 0 5 4  5 7  5 2 . 1.287462
"       2  34" 13 2  8 6 0 6 4  5 7 20 2 0 1.287462
"       2  34" 13 2  8 7 0 7 4  5 7 15 1 0 1.287462
"       2  34" 13 2  8 8 0 8 4  3 3 42 1 0 1.287462
"       2  35" 13 2  2 1 1 1 2  1 1 55 1 0 1.287462
"       2  35" 13 2  2 2 0 2 2  2 2 47 2 0 1.287462
"       2  40" 13 2  5 1 1 1 2  1 1 65 1 1 1.287462
"       2  40" 13 2  5 2 0 2 2  2 2 66 2 1 1.287462
"       2  40" 13 2  5 3 0 3 2  5 7 10 1 . 1.287462
"       2  40" 13 2  5 4 0 4 2  5 7  8 2 . 1.287462
"       2  40" 13 2  5 5 0 5 2  5 7  8 1 . 1.287462
"       2  46" 13 2  3 1 1 1 1  1 1 79 2 3 1.287462
"       2  46" 13 2  3 2 0 2 1  5 7 13 2 . 1.287462
"       2  46" 13 2  3 3 0 3 1  5 7 14 1 . 1.287462
"       2  48" 13 2  1 1 1 1 1  1 1 54 2 3 1.287462
"       2  50" 13 2  4 1 1 1 4  1 1 54 2 3 1.287462
"       2  50" 13 2  4 2 0 2 4  3 3 18 2 0 1.287462
"       2  50" 13 2  4 3 0 3 4  5 7  0 1 . 1.287462
"       2  50" 13 2  4 4 0 4 4 10 8 65 2 3 1.287462
"       2  59" 13 2  3 1 1 1 4  1 1 57 1 0 1.287462
"       2  59" 13 2  3 2 0 2 4  2 2 47 2 0 1.287462
"       2  59" 13 2  3 3 0 3 4  5 7 16 1 0 1.287462
"       2  65" 13 2  2 1 1 1 5  1 1 65 2 1 1.287462
"       2  65" 13 2  2 2 0 2 5 12 9 39 2 1 1.287462
"       2  75" 13 2  5 1 1 1 4  1 1 45 2 4 1.287462
"       2  75" 13 2  5 2 0 2 4  3 3 31 2 0 1.287462
"       2  75" 13 2  5 3 0 3 4  3 3 22 2 0 1.287462
"       2  75" 13 2  5 4 0 4 4  5 7  2 1 . 1.287462
"       2  75" 13 2  5 5 0 5 4  5 7  0 1 . 1.287462
"       2  77" 13 2  4 1 1 1 1  1 1 36 2 0 1.287462
"       2  77" 13 2  4 2 0 2 1  3 3 14 2 . 1.287462
"       2  77" 13 2  4 3 0 3 1  3 3 10 2 . 1.287462
"       2  77" 13 2  4 4 0 4 1  3 3  9 1 . 1.287462
"       2  78" 13 2  5 1 1 1 4  1 1 63 2 1 1.287462
"       2  78" 13 2  5 2 0 2 4  3 3 29 2 0 1.287462
"       2  78" 13 2  5 3 0 3 4  3 3 17 1 0 1.287462
"       2  78" 13 2  5 4 0 4 4  5 7  8 2 . 1.287462
"       2  78" 13 2  5 5 0 5 4  5 7  6 2 . 1.287462
"       2  79" 13 2  4 1 1 1 3  1 1 48 2 1 1.287462
"       2  79" 13 2  4 2 0 2 3 10 8 35 2 0 1.287462
"       2  79" 13 2  4 3 0 3 3  5 7 13 2 . 1.287462
"       2  79" 13 2  4 4 0 4 3  3 3  8 2 . 1.287462
"       2  80" 13 2  2 1 1 1 2  1 1 38 1 0 1.287462
"       2  80" 13 2  2 2 0 2 2  8 4 26 2 0 1.287462
"       2  92" 13 2  4 1 1 1 4  1 1 81 2 0 1.287462
"       2  92" 13 2  4 2 0 2 4  5 7 23 1 0 1.287462
"       2  92" 13 2  4 3 0 3 4  5 7 10 2 . 1.287462
"       2  92" 13 2  4 4 0 4 4  5 7 21 1 0 1.287462
"       2  96" 13 2 14 1 1 1 4  1 1 70 1 1 1.287462
"       2  96" 13 2 14 2 0 2 4  2 2 64 2 1 1.287462
"       2  96" 13 2 14 3 0 3 4  3 3 41 2 0 1.287462
"       2  96" 13 2 14 4 0 4 4  5 7 10 1 . 1.287462
end
label values relation_structure relation_structure
label def relation_structure 1 "one adult", modify
label def relation_structure 2 "two adults, opposite sex", modify
label def relation_structure 3 "two adults, same sex", modify
label def relation_structure 4 "three+ related adults", modify
label def relation_structure 5 "unrelated adults", modify
label values rel_to_head rel_to_head
label def rel_to_head 1 "head", modify
label def rel_to_head 2 "wife or husband", modify
label def rel_to_head 3 "son/daughter", modify
label def rel_to_head 4 "son/daughter-in-law", modify
label def rel_to_head 5 "grandchild", modify
label def rel_to_head 8 "brother/sister", modify
label def rel_to_head 10 "other relative", modify
label def rel_to_head 12 "not related", modify
label values relationship_derived relationship_derived
label def relationship_derived 1 "Head/acting head", modify
label def relationship_derived 2 "Husband/wife/partner", modify
label def relationship_derived 3 "Son/daughter/stepchild/adopted child", modify
label def relationship_derived 4 "Brother/sister/stepbrother/stepsister", modify
label def relationship_derived 7 "Grandchild/great grandchild", modify
label def relationship_derived 8 "Other relative", modify
label def relationship_derived 9 "Non-related persons", modify
label values Age age
label values Sex sex
label def sex 1 "male", modify
label def sex 2 "female", modify
label values marital_status marital_status
label def marital_status 0 "never married", modify
label def marital_status 1 "married", modify
label def marital_status 3 "widowed", modify
label def marital_status 4 "divorced/separated", modify
label var hhid "Household identifier" 
label var strata "stratification" 
label var psu "Primary sampling unit" 
label var hhsize "Household size" 
label var pid "Person identifier" 
label var hhtag "tag(hhid)" 
label var line_number "Line number" 
label var relation_structure "Relationship structure" 
label var rel_to_head "Relationship to head" 
label var relationship_derived "Relationship to head" 
label var Age "Age" 
label var Sex "Sex" 
label var marital_status "Marital status" 
label var person_wgt "Weight variable"

[Clyde Schechter]

The following code will get you the first four categories of the five you request:

label list rel_to_head
by hhid, sort: egen n_adults = total(inrange(Age, 18, .))
by hhid, sort: egen sex_head = max(cond(rel_to_head == 1, Sex, .))
by hhid, sort: egen sex_spouse = max(cond(rel_to_head == 2, Sex, .))

gen byte wanted = 0 if n_adults == 0
replace wanted = 1 if n_adults == 1
replace wanted = 2 if n_adults == 2 & sex_head != sex_spouse
replace wanted = 3 if n_adults == 2 & sex_head == sex_spouse

But categories 4 and 5 are ill-defined because a household could contain more than three adults, some of whom are related, and others not. It is unclear which way you want to classify those.

[Dubile Boko]

Morning Clyde,

Thank you so much for your assistance. The variable referring to related adults is a combination of [inlist(rel_to_head,4,5,8,10)] where age is 18+. Unrelated is (rel_to_head = 12).

I have tried to work further on the code you provided, but I can't seem to be getting what I want right (4 and 5 are currently not counting the number of individuals in the household). I also do not see how this could possibly work, since some households will have an overlap of relationships where 2 and 3 will sometimes overlap with 4 and 5. In essence, I want a situation where if a household has already been allocated to 2 and 3, then it cannot be allocated to 4 and 5. Thank you.

cap drop related
gen related = 0
replace related = 1 if inlist(rel_to_head,4,5,8,10)
tab related, m

cap drop unrelated
gen unrelated = 0
replace unrelated = 1 if rel_to_head==12
tab unrelated, m

label list rel_to_head
by hhid, sort: egen n_adults = total(inrange(Age, 18, .))
by hhid, sort: egen sex_head = max(cond(rel_to_head == 1, Sex, .))
by hhid, sort: egen sex_spouse = max(cond(rel_to_head == 2, Sex, .))

cap drop wanted
gen byte wanted = 0 if n_adults == 0
replace wanted = 1 if n_adults == 1
replace wanted = 2 if n_adults == 2 & sex_head != sex_spouse
replace wanted = 3 if n_adults == 2 & sex_head == sex_spouse
replace wanted = 4 if related == 1 & Age > 17
replace wanted = 5 if unrelated == 1 & Age > 17
tab wanted, m

[Clyde Schechter]

Yes, I understood how to determine when there is an unrelated adult. You didn't answer my question: if a household contains 3 or more adults, but no spouses, and some of them are related and others are not, how do you decide if this should be group 4 or group 5. Without an answer to that, I can't help you further.

Your code above cannot work. The variable related that you create only identifies the particular individual(s) who are unrelated to the head. It does not characterize the household as to whether it contains any such people, nor how many. To do that wlil require an -egen- function.

It is easy enough to prevent a household already classified as 1, 2, or 3, from being changed into a 4 or 5 by just adding -& missing(wanted)- to the -if- condition in the -replace wanted = ...- commands for 4 and 5.