Coding parental status in longitudinal data with multiple family members (i.e., parents + children) observed

[Claire McKenna]

I'm working with longitudinal data that, for every family unit observed, includes observations for both parents and children. I'm trying to code parental status, using the mother and father ID numbers (MOM_ID DAD_ID), and ages (AGE), attached to each respondent labeled a child (RELATE). Specifically, I want to create three new variables:

1. OWNKID: for each parent observation, count of all children in the family, regardless of age
2. OWNKID_18: for each parent observation, count of all children in the family younger than 18
3. OWNKID_5: for each parent observation, count of all children in the family younger than 5

Below is a sample of the data I'm trying to generate -- with the three variables I define above filled in with what I'm looking for. I've been trying to apply the loop suggested in number 6 on this page: https://www.stata.com/support/faqs/d...ng-properties/. But it's only able to generate a variable that tells me, 0/1, whether someone has a child (w/ my slight adjustments). And I'm not sure where in the loop to account for the child's age. Thank you for any help you can provide!

PERSON_ID	FAMILY_ID	Y_M	WIFAM_PERSON_ID	MOM_ID	DAD_ID	AGE	RELATE	OWNKID	OWNKID_18	OWNKID_5
1234	101	2020_1	1	0	0	33	hh-head	1	1	1
1234	101	2020_2	1	0	0	33	hh-head	1	1	1
1234	101	2020_3	1	0	0	33	hh-head	1	1	1
1235	101	2020_1	2	0	0	35	spouse	1	1	1
1235	101	2020_2	2	0	0	35	spouse	1	1	1
1235	101	2020_3	2	0	0	35	spouse	1	1	1
1236	101	2020_1	3	1	2	4	child	0	0	0
1236	101	2020_2	3	1	2	4	child	0	0	0
1236	101	2020_3	3	1	2	4	child	0	0	0
1237	202	2020_1	1	0	0	49	hh-head	2	1	0
1237	202	2020_2	1	0	0	49	hh-head	2	1	0
1238	202	2020_1	2	0	0	54	spouse	2	1	0
1238	202	2020_2	2	0	0	54	spouse	2	1	0
1239	202	2020_1	3	1	2	19	child	0	0	0
1239	202	2020_2	3	1	2	19	child	0	0	0
1240	202	2020_1	4	1	2	16	child	0	0	0
1240	202	2020_2	4	1	2	16	child	0	0	0

[Clyde Schechter]

clear*

* Example generated by -dataex-. For more info, type help dataex
clear
input int(person_id family_id) str7 y_m byte(wifam_person_id mom_id dad_id age) str8 relate
1234 101 "2020_1 " 1 0 0 33 "hh-head "
1234 101 "2020_2 " 1 0 0 33 "hh-head "
1234 101 "2020_3 " 1 0 0 33 "hh-head "
1235 101 "2020_1 " 2 0 0 35 "spouse "
1235 101 "2020_2 " 2 0 0 35 "spouse "
1235 101 "2020_3 " 2 0 0 35 "spouse "
1236 101 "2020_1 " 3 1 2  4 "child "  
1236 101 "2020_2 " 3 1 2  4 "child "  
1236 101 "2020_3 " 3 1 2  4 "child "  
1237 202 "2020_1 " 1 0 0 49 "hh-head "
1237 202 "2020_2 " 1 0 0 49 "hh-head "
1238 202 "2020_1 " 2 0 0 54 "spouse "
1238 202 "2020_2 " 2 0 0 54 "spouse "
1239 202 "2020_1 " 3 1 2 19 "child "  
1239 202 "2020_2 " 3 1 2 19 "child "  
1240 202 "2020_1 " 4 1 2 16 "child "  
1240 202 "2020_2 " 4 1 2 16 "child "  
end


frame put person_id family_id y_m wifam_person_id mom_id dad_id age, into(offspring)
frame change offspring
drop if mom_id == 0 & dad_id == 0
mvdecode mom_id dad_id, mv(0)
rename mom_id idmom
rename dad_id iddad
reshape long id, i(person_id family_id y_m) j(parent)string
rename id id_parent
tempfile offspring
save `offspring'

frame change default

rangejoin id_parent wifam_person_id wifam_person_id using `offspring', ///
    by(y_m family_id)
by person_id family_id y_m, sort: egen ownkid = count(id_parent)
by person_id family_id y_m: egen ownkid_18 = total(cond(age_U < 18, !missing(id_parent), .))
by person_id family_id y_m: egen ownkid_5 = total(cond(age_U < 5, !missing(id_parent), .))

by person_id family_id y_m, sort: keep if _n == 1
drop *_U id_parent

This code uses frames, and so it requires version 16 or later. -rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Claire McKenna]

This seemed to work. Thank you so much! I really appreciate it.