Hi, I need to generate a new variable from the dataset that contains the number of children in the family age under 17. In other words, I need to count the number of children under age 17 in each household.
Among the variables, “familyid” is the id for families and each member has the same family id, “sex” defines the sex of the family members where 1 is for male and 2 is for female, “family_relation” establishes the relationship of the family members with the reference person (family head).
In the “family_relation” 1: Reference person with related persons in the household, 2: Reference Person without related persons in the household, 3: Spouse of reference person, 4: Child of reference person, 5: Grandchild of reference person, 6: Parent of reference person, 7: Brother/sister of reference person, 8: Other relatives of reference person, 9: Foster child of reference person, 10: Unmarried partner of reference person, 11: Housemate/roommate, 12: Roomer/boarder, 13: Other non-Child of the reference person. From this, we need 4: Child of the reference person.

I do appreciate your support in advance!


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double familyid int year str9 month byte(sex age family_relation)
19128000276 2008 "June"      1 29 1
19128000276 2008 "July"      1 29 1
19128000276 2008 "August"    1 29 1
19128000276 2008 "September" 1 29 1
19128000276 2008 "October"   1 29 1
19128000276 2008 "November"  1 29 1
19128000276 2008 "December"  1 29 1
19128000276 2009 "January"   1 29 1
19128000276 2009 "February"  1 30 1
19128000276 2009 "March"     1 30 1
19128000276 2009 "April"     1 30 1
19128000276 2009 "May"       1 30 1
19128000276 2009 "June"      1 30 1
19128000276 2009 "July"      1 30 1
19128000276 2009 "August"    1 30 1
19128000276 2009 "September" 1 30 1
19128000276 2009 "October"   1 30 1
19128000276 2009 "November"  1 30 1
19128000276 2009 "December"  1 30 1
19128000276 2010 "January"   1 30 1
19128000276 2010 "February"  1 31 1
19128000276 2010 "March"     1 31 1
19128000276 2010 "April"     1 31 1
19128000276 2010 "May"       1 31 1
19128000276 2010 "June"      1 31 1
19128000276 2010 "July"      1 31 1
19128000276 2010 "August"    1 31 1
19128000276 2008 "June"      2 32 3
19128000276 2008 "July"      2 32 3
19128000276 2008 "August"    2 32 3
19128000276 2008 "September" 2 32 3
19128000276 2008 "October"   2 32 3
19128000276 2008 "November"  2 32 3
19128000276 2008 "December"  2 32 3
19128000276 2009 "January"   2 32 3
19128000276 2009 "February"  2 32 3
19128000276 2009 "March"     2 32 3
19128000276 2009 "April"     2 32 3
19128000276 2009 "May"       2 32 3
19128000276 2009 "June"      2 33 3
19128000276 2009 "July"      2 33 3
19128000276 2009 "August"    2 33 3
19128000276 2009 "September" 2 33 3
19128000276 2009 "October"   2 33 3
19128000276 2009 "November"  2 33 3
19128000276 2009 "December"  2 33 3
19128000276 2010 "January"   2 33 3
19128000276 2010 "February"  2 33 3
19128000276 2010 "March"     2 33 3
19128000276 2010 "April"     2 33 3
19128000276 2010 "May"       2 33 3
19128000276 2010 "June"      2 34 3
19128000276 2010 "July"      2 34 3
19128000276 2010 "August"    2 34 3
19128000276 2008 "June"      1  6 4
19128000276 2008 "July"      1  6 4
19128000276 2008 "August"    1  6 4
19128000276 2008 "September" 1  6 4
19128000276 2008 "October"   1  7 4
19128000276 2008 "November"  1  7 4
19128000276 2008 "December"  1  7 4
19128000276 2009 "January"   1  7 4
19128000276 2009 "February"  1  7 4
19128000276 2009 "March"     1  7 4
19128000276 2009 "April"     1  7 4
19128000276 2009 "May"       1  7 4
19128000276 2009 "June"      1  7 4
19128000276 2009 "July"      1  7 4
19128000276 2009 "August"    1  7 4
19128000276 2009 "September" 1  7 4
19128000276 2009 "October"   1  8 4
19128000276 2009 "November"  1  8 4
19128000276 2009 "December"  1  8 4
19128000276 2010 "January"   1  8 4
19128000276 2010 "February"  1  8 4
19128000276 2010 "March"     1  8 4
19128000276 2010 "April"     1  8 4
19128000276 2010 "May"       1  8 4
19128000276 2010 "June"      1  8 4
19128000276 2010 "July"      1  8 4
19128000276 2010 "August"    1  8 4
19128000334 2008 "June"      1 23 1
19128000334 2008 "July"      1 23 1
19128000334 2008 "August"    1 23 1
19128000334 2008 "September" 1 23 1
19128000334 2008 "October"   1 23 1
19128000334 2008 "November"  1 23 1
19128000334 2008 "December"  1 23 1
19128000334 2009 "June"      1 24 8
19128000334 2009 "July"      1 24 8
19128000334 2009 "August"    1 24 8
19128000334 2009 "September" 1 24 1
19128000334 2009 "October"   1 24 1
19128000334 2009 "November"  1 24 1
19128000334 2009 "December"  1 24 1
19128000334 2010 "January"   1 24 1
19128000334 2010 "February"  1 24 1
19128000334 2010 "March"     1 24 1
19128000334 2010 "April"     1 24 1
19128000334 2010 "May"       1 25 1
end