Households

[Femi Oladunni]

Hi Everyone,

Below is a hypothetical data set of 10 different households (HHID).

* Example generated by -dataex-. To install:    ssc install dataex
clear
input byte(HHID Indiv Male) double Age str13    Relationship byte(Can_read    Can_write    size)
1 1 1  75 "Husband Alone" 1 1 1
2 1 1  67 "Husband "      0 0 4
2 3 0  40 "Daughter"      0 0 4
2 4 0  14 "Daughter"      1 1 4
2 2 0  74 "Wife"          1 1 4
3 2 1  24 "Son"           0 0 2
3 1 1  65 "Husband Alone" 0 0 2
4 3 1  15 "Son"           1 1 5
4 5 0   2 "Daughter"      0 0 5
4 1 1  40 "Husband"       1 1 5
4 4 0   6 "Daughter"      0 0 5
4 2 0  41 "Wife"          1 1 5
5 6 0   6 "Daugther"      0 0 7
5 3 1  19 "Son"           1 1 7
5 5 0  12 "Daughter"      0 0 7
5 2 0  45 "Wife"          1 1 7
5 7 1   3 "Son"           0 0 7
5 4 1  15 "Son"           1 1 7
5 1 1  53 "Husband"       0 0 7
6 2 0  24 "Daughter"      0 0 4
6 3 1  19 "Son"           0 0 4
6 4 0  19 "Daughter"      0 0 4
6 1 1  26 "Son"           0 0 4
7 4 0   1 "Daughter"      0 0 4
7 3 0   2 "Daughter"      0 0 4
7 2 0  26 "Wife"          0 0 4
7 1 1  27 "Husband"       1 1 4
8 1 1  42 "Husband"       1 1 3
8 3 1  11 "Son"           1 1 3
8 2 0  40 "Wife"          0 0 3
9 3 1  19 "Son"           1 1 5
9 1 1  50 "Husband"       0 0 5
9 4 1  15 "Son"           1 1 5
9 2 0  49 "Wife"          1 1 5
9 5 1  10 "Son"           0 0 5
10 1 1  40 "Husband"       0 0 8
10 5 1   3 "Son"           0 0 8
10 6 0   2 "Daughter"      0 0 8
10 3 1  10 "Son"           1 1 8
10 2 0  30 "Wife"          1 1 8
10 4 1   6 "Son"           0 0 8
10 8 0 .01 "Daughter"      0 0 8
10 7 0 .01 "Daughter"      0 0 8
end

My objectives are to create the following variable;

• Literacy rate: for an individual who can read and write
• Child literacy rate: for an individual who is between ages 5 and 15 that can read and write
• Father literacy rate: for a child who has a father that can read and write
• Mother literacy rate: for a child who has a mother that can read and write
• Number of children between ages 5 and 15 in a household
• Number of siblings a child has
• Number of female siblings a child has
• Number of male siblings a child has
• Number of older siblings a child has
• Number of younger siblings a child has

Please your help/input will be highly appreciated!

Regards

[Carlo Lazzaro]

Femi:
has your post something to do with https://www.statalist.org/forums/help#adviceextras #4?

[Femi Oladunni]

Hi Carlo,
Please this is not homework but present research that I am working on. I apologize if I should have stated that earlier on.

[Carlo Lazzaro]

Femi:
due to time constraints I can provide you with the codes for the first two items:

 gen Literacy_rate=1 if Can_read==1 & Can_write==1


. gen Child_literacy_rate=1 if Can_read==1 & Can_write==1 & Age<15 & Age>5

In addition, please take a look at the way -Relationship- data were entered:

. tab Relationship_num

Relationship_ |
          num |      Freq.     Percent        Cum.
--------------+-----------------------------------
     Daughter |         12       27.91       27.91
     Daugther |          1        2.33       30.23
      Husband |          6       13.95       44.19
     Husband  |          1        2.33       46.51
Husband Alone |          2        4.65       51.16
          Son |         14       32.56       83.72
         Wife |          7       16.28      100.00
--------------+-----------------------------------
        Total |         43      100.00

[Andrew Musau]

Adding to Carlo's helpful reply and suggestions, it may not be wise to measure literacy for kids less than 6 years old because they are not expected to be literate. As your variables are 0/1 indicators, and the mean of such an indicator represents the proportion of ones, the literacy rates for the subgroups are just the means multiplied by 100 (given by the percentage column in Carlo's table).

*Before running Carlo's codes
replace Relationship= trim(itrim(Relationship))

Then:

*Number of children between ages 5 and 15 in a household
bys HHID: egen wanted1= total(cond((Relationship=="Son"|Relationship=="Daughter") & inrange(Age, 5, 15), 1, .))

No. of siblings a child has is the total number of kids in the household less one. For the male-female restrictions, count the number of male/female kids and take away one depending on the gender and count.

*Number of kids in a household
bys HHID: egen nkids= total(Relationship=="Son"|Relationship=="Daughter")

*Number of older siblings a child has
bys HHID (Age): gen rank= sum(Relationship=="Son"|Relationship=="Daughter") if Relationship=="Son"|Relationship=="Daughter"
bys HHID: gen wanted2= nkids- rank

*Number of younger siblings a child has
bys HHID: gen wanted3= rank-1

Res.:

. sort HHID Age

. l, sepby(HHID)

     +---------------------------------------------------------------------------------------------------------------------+
     | HHID   Indiv   Male   Age    Relationship   Can_read   Can_wr~e   size   wanted1   nkids   rank   wanted2   wanted3 |
     |---------------------------------------------------------------------------------------------------------------------|
  1. |    1       1      1    75   Husband Alone          1          1      1         0       0      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
  2. |    2       4      0    14        Daughter          1          1      4         1       2      1         1         0 |
  3. |    2       3      0    40        Daughter          0          0      4         1       2      2         0         1 |
  4. |    2       1      1    67        Husband           0          0      4         1       2      .         .         . |
  5. |    2       2      0    74            Wife          1          1      4         1       2      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
  6. |    3       2      1    24             Son          0          0      2         0       1      1         0         0 |
  7. |    3       1      1    65   Husband Alone          0          0      2         0       1      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
  8. |    4       5      0     2        Daughter          0          0      5         2       3      1         2         0 |
  9. |    4       4      0     6        Daughter          0          0      5         2       3      2         1         1 |
 10. |    4       3      1    15             Son          1          1      5         2       3      3         0         2 |
 11. |    4       1      1    40         Husband          1          1      5         2       3      .         .         . |
 12. |    4       2      0    41            Wife          1          1      5         2       3      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
 13. |    5       7      1     3             Son          0          0      7         2       4      1         3         0 |
 14. |    5       6      0     6        Daugther          0          0      7         2       4      .         .         . |
 15. |    5       5      0    12        Daughter          0          0      7         2       4      2         2         1 |
 16. |    5       4      1    15             Son          1          1      7         2       4      3         1         2 |
 17. |    5       3      1    19             Son          1          1      7         2       4      4         0         3 |
 18. |    5       2      0    45            Wife          1          1      7         2       4      .         .         . |
 19. |    5       1      1    53         Husband          0          0      7         2       4      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
 20. |    6       4      0    19        Daughter          0          0      4         0       4      1         3         0 |
 21. |    6       3      1    19             Son          0          0      4         0       4      2         2         1 |
 22. |    6       2      0    24        Daughter          0          0      4         0       4      3         1         2 |
 23. |    6       1      1    26             Son          0          0      4         0       4      4         0         3 |
     |---------------------------------------------------------------------------------------------------------------------|
 24. |    7       4      0     1        Daughter          0          0      4         0       2      1         1         0 |
 25. |    7       3      0     2        Daughter          0          0      4         0       2      2         0         1 |
 26. |    7       2      0    26            Wife          0          0      4         0       2      .         .         . |
 27. |    7       1      1    27         Husband          1          1      4         0       2      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
 28. |    8       3      1    11             Son          1          1      3         1       1      1         0         0 |
 29. |    8       2      0    40            Wife          0          0      3         1       1      .         .         . |
 30. |    8       1      1    42         Husband          1          1      3         1       1      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
 31. |    9       5      1    10             Son          0          0      5         2       3      1         2         0 |
 32. |    9       4      1    15             Son          1          1      5         2       3      2         1         1 |
 33. |    9       3      1    19             Son          1          1      5         2       3      3         0         2 |
 34. |    9       2      0    49            Wife          1          1      5         2       3      .         .         . |
 35. |    9       1      1    50         Husband          0          0      5         2       3      .         .         . |
     |---------------------------------------------------------------------------------------------------------------------|
 36. |   10       7      0   .01        Daughter          0          0      8         2       6      1         5         0 |
 37. |   10       8      0   .01        Daughter          0          0      8         2       6      2         4         1 |
 38. |   10       6      0     2        Daughter          0          0      8         2       6      3         3         2 |
 39. |   10       5      1     3             Son          0          0      8         2       6      4         2         3 |
 40. |   10       4      1     6             Son          0          0      8         2       6      5         1         4 |
 41. |   10       3      1    10             Son          1          1      8         2       6      6         0         5 |
 42. |   10       2      0    30            Wife          1          1      8         2       6      .         .         . |
 43. |   10       1      1    40         Husband          0          0      8         2       6      .         .         . |
     +---------------------------------------------------------------------------------------------------------------------+

.

[Hemanshu Kumar]

Here is some code that I believe improves on #4 and #5. In particular:

• I define literacy only for ages 5 and above; the variable is coded missing for younger children (not 0)
• I compute variables corresponding to all your questions
• the code in #5 does not account for one misspelling of "Daughter" in HHID 5
• the code in #5 does not account for the possibility of multiple children of the same age, as seen in HHID 6 and 10.

rename * , lower
replace relationship = trim(itrim(relationship))
replace relationship = "Daughter" if relationship == "Daugther"

gen literate = (can_read & can_write) if age>=5
gen byte is_child = inlist(relationship,"Son","Daughter")
gen byte is_kid = is_child & inrange(age,5,15)
gen kid_literate = literate if is_kid

sort hhid

by hhid: egen byte has_father = max(strpos(relationship,"Husband")>0)
by hhid: egen byte has_mother = max(relationship == "Wife")

by hhid: egen byte father_literate = max(strpos(relationship,"Husband") & literate)
replace father_literate = . if !is_child | !has_father

by hhid: egen byte mother_literate = max(relationship == "Wife" & literate)
replace mother_literate = . if !is_child | !has_mother

by hhid: egen num_children = total(is_child)

by hhid: egen num_kids = total(is_kid)

by hhid: egen num_sibs = total(is_child) if is_child
replace num_sibs = num_sibs - 1 if is_child

by hhid: egen num_female_sibs = total(relationship == "Daughter") if is_child
replace num_female_sibs = num_female_sibs - 1 if relationship == "Daughter"

by hhid: egen num_male_sibs = total(relationship == "Son") if is_child
replace num_male_sibs = num_male_sibs - 1 if relationship == "Son"

by hhid: egen sib_rank = rank(age) if is_child
gen num_older_sibs = floor(num_children - sib_rank) if is_child
gen num_younger_sibs = floor(sib_rank) - 1 if is_child

Here is what we get for the literacy-related variables:

gsort hhid -age
noi li hhid indiv age relationship can_read can_write literate kid_literate father_literate mother_literate, sepby(hhid) noobs

  +------------------------------------------------------------------------------------------------------+
  | hhid   indiv   age    relationship   can_read   can_wr~e   literate   kid_li~e   father~e   mother~e |
  |------------------------------------------------------------------------------------------------------|
  |    1       1    75   Husband Alone          1          1          1          .          .          . |
  |------------------------------------------------------------------------------------------------------|
  |    2       2    74            Wife          1          1          1          .          .          . |
  |    2       1    67         Husband          0          0          0          .          .          . |
  |    2       3    40        Daughter          0          0          0          .          0          1 |
  |    2       4    14        Daughter          1          1          1          1          0          1 |
  |------------------------------------------------------------------------------------------------------|
  |    3       1    65   Husband Alone          0          0          0          .          .          . |
  |    3       2    24             Son          0          0          0          .          0          . |
  |------------------------------------------------------------------------------------------------------|
  |    4       2    41            Wife          1          1          1          .          .          . |
  |    4       1    40         Husband          1          1          1          .          .          . |
  |    4       3    15             Son          1          1          1          1          1          1 |
  |    4       4     6        Daughter          0          0          0          0          1          1 |
  |    4       5     2        Daughter          0          0          .          .          1          1 |
  |------------------------------------------------------------------------------------------------------|
  |    5       1    53         Husband          0          0          0          .          .          . |
  |    5       2    45            Wife          1          1          1          .          .          . |
  |    5       3    19             Son          1          1          1          .          0          1 |
  |    5       4    15             Son          1          1          1          1          0          1 |
  |    5       5    12        Daughter          0          0          0          0          0          1 |
  |    5       6     6        Daughter          0          0          0          0          0          1 |
  |    5       7     3             Son          0          0          .          .          0          1 |
  |------------------------------------------------------------------------------------------------------|
  |    6       1    26             Son          0          0          0          .          .          . |
  |    6       2    24        Daughter          0          0          0          .          .          . |
  |    6       4    19        Daughter          0          0          0          .          .          . |
  |    6       3    19             Son          0          0          0          .          .          . |
  |------------------------------------------------------------------------------------------------------|
  |    7       1    27         Husband          1          1          1          .          .          . |
  |    7       2    26            Wife          0          0          0          .          .          . |
  |    7       3     2        Daughter          0          0          .          .          1          0 |
  |    7       4     1        Daughter          0          0          .          .          1          0 |
  |------------------------------------------------------------------------------------------------------|
  |    8       1    42         Husband          1          1          1          .          .          . |
  |    8       2    40            Wife          0          0          0          .          .          . |
  |    8       3    11             Son          1          1          1          1          1          0 |
  |------------------------------------------------------------------------------------------------------|
  |    9       1    50         Husband          0          0          0          .          .          . |
  |    9       2    49            Wife          1          1          1          .          .          . |
  |    9       3    19             Son          1          1          1          .          0          1 |
  |    9       4    15             Son          1          1          1          1          0          1 |
  |    9       5    10             Son          0          0          0          0          0          1 |
  |------------------------------------------------------------------------------------------------------|
  |   10       1    40         Husband          0          0          0          .          .          . |
  |   10       2    30            Wife          1          1          1          .          .          . |
  |   10       3    10             Son          1          1          1          1          0          1 |
  |   10       4     6             Son          0          0          0          0          0          1 |
  |   10       5     3             Son          0          0          .          .          0          1 |
  |   10       6     2        Daughter          0          0          .          .          0          1 |
  |   10       7   .01        Daughter          0          0          .          .          0          1 |
  |   10       8   .01        Daughter          0          0          .          .          0          1 |
  +------------------------------------------------------------------------------------------------------+

And here is what you get on siblings:

noi li hhid indiv age relationship num_kids num_sibs num_female_sibs num_male_sibs num_older_sibs num_younger_sibs, sepby(hhid) noobs

   +------------------------------------------------------------------------------------------------------+
  | hhid   indiv   age    relationship   num_kids   num_sibs   num_fe~s   num_ma~s   num_ol~s   num_yo~s |
  |------------------------------------------------------------------------------------------------------|
  |    1       1    75   Husband Alone          0          .          .          .          .          . |
  |------------------------------------------------------------------------------------------------------|
  |    2       2    74            Wife          1          .          .          .          .          . |
  |    2       1    67         Husband          1          .          .          .          .          . |
  |    2       3    40        Daughter          1          1          1          0          0          1 |
  |    2       4    14        Daughter          1          1          1          0          1          0 |
  |------------------------------------------------------------------------------------------------------|
  |    3       1    65   Husband Alone          0          .          .          .          .          . |
  |    3       2    24             Son          0          0          0          0          0          0 |
  |------------------------------------------------------------------------------------------------------|
  |    4       2    41            Wife          2          .          .          .          .          . |
  |    4       1    40         Husband          2          .          .          .          .          . |
  |    4       3    15             Son          2          2          2          0          0          2 |
  |    4       4     6        Daughter          2          2          1          1          1          1 |
  |    4       5     2        Daughter          2          2          1          1          2          0 |
  |------------------------------------------------------------------------------------------------------|
  |    5       1    53         Husband          3          .          .          .          .          . |
  |    5       2    45            Wife          3          .          .          .          .          . |
  |    5       3    19             Son          3          4          2          2          0          4 |
  |    5       4    15             Son          3          4          2          2          1          3 |
  |    5       5    12        Daughter          3          4          1          3          2          2 |
  |    5       6     6        Daughter          3          4          1          3          3          1 |
  |    5       7     3             Son          3          4          2          2          4          0 |
  |------------------------------------------------------------------------------------------------------|
  |    6       1    26             Son          0          3          2          1          0          3 |
  |    6       2    24        Daughter          0          3          1          2          1          2 |
  |    6       4    19        Daughter          0          3          1          2          2          0 |
  |    6       3    19             Son          0          3          2          1          2          0 |
  |------------------------------------------------------------------------------------------------------|
  |    7       1    27         Husband          0          .          .          .          .          . |
  |    7       2    26            Wife          0          .          .          .          .          . |
  |    7       3     2        Daughter          0          1          1          0          0          1 |
  |    7       4     1        Daughter          0          1          1          0          1          0 |
  |------------------------------------------------------------------------------------------------------|
  |    8       1    42         Husband          1          .          .          .          .          . |
  |    8       2    40            Wife          1          .          .          .          .          . |
  |    8       3    11             Son          1          0          0          0          0          0 |
  |------------------------------------------------------------------------------------------------------|
  |    9       1    50         Husband          2          .          .          .          .          . |
  |    9       2    49            Wife          2          .          .          .          .          . |
  |    9       3    19             Son          2          2          0          2          0          2 |
  |    9       4    15             Son          2          2          0          2          1          1 |
  |    9       5    10             Son          2          2          0          2          2          0 |
  |------------------------------------------------------------------------------------------------------|
  |   10       1    40         Husband          2          .          .          .          .          . |
  |   10       2    30            Wife          2          .          .          .          .          . |
  |   10       3    10             Son          2          5          3          2          0          5 |
  |   10       4     6             Son          2          5          3          2          1          4 |
  |   10       5     3             Son          2          5          3          2          2          3 |
  |   10       6     2        Daughter          2          5          2          3          3          2 |
  |   10       7   .01        Daughter          2          5          2          3          4          0 |
  |   10       8   .01        Daughter          2          5          2          3          4          0 |
  +------------------------------------------------------------------------------------------------------+

[Femi Oladunni]

Thanks a lot for your input Carlo, Andre, and Hemanshu!!!

[Sergiy Radyakin]

Femi Oladunni

your hypothetical data looks good, but the real survey data will likely be not so clean. Since you are listing the [redundant] size variable, perhaps add a check:

egen hhsize=count(HHID), by(HHID)
assert size==hhsize

Likewise you may want to add checks for "only one hh head in each hh", "only one spouse in each hh"(if applicable), "child younger than parent by N years" (if biological), and other typical checks for household relationships.

Best, Sergiy