Identifying mother's birth age in each household

[Zijun Lin]

I am trying to find out the mother's birth age (delivery age) for the OLDEST CHILD in each household, but not sure how to do this in stata.

SUID: household identifier
ADDID: address id
PNUM: person number
EPNMOM: person number of mother

sample dataset:

SUID	ADDID	PNUM	SEX (1=M,2=F)	EPNMOM	AGE
1	11	101	1	.	39
1	11	102	2	.	38
1	11	103	1	102	5
1	11	104	1	102	3
2	11	101	2	.	55
2	11	102	2	101	20
2	12	101	2	.	40
2	12	102	1	.	35
2	12	103	2	101	10

expected output:

SUID	ADDID	PNUM	SEX (1=M,2=F)	EPNMOM	AGE	BIRTH AGE
1	11	101	1	.	39	.
1	11	102	2	.	38	33
1	11	103	1	102	5	.
1	11	104	1	102	3	.
2	11	101	2	.	55	35
2	11	102	2	101	20	.
2	12	101	2	.	40	30
2	12	102	1	.	35	.
2	12	103	2	101	10	.

Thank you!

[Nick Cox]

I am puzzled by the second household in which pnum is repeated.

[Chen Samulsion]

The -rangestat- is still suitable to tackle this question as in the last thread of yours. https://www.statalist.org/forums/for...same-household

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(suid addid) int pnum byte sex1m2f int epnmom byte age
1 11 101 1   . 39
1 11 102 2   . 38
1 11 103 1 102  5
1 11 104 1 102  3
2 11 101 2   . 55
2 11 102 2 101 20
2 12 101 2   . 40
2 12 102 1   . 35
2 12 103 2 101 10
end

clonevar mom_id = epnmom
replace mom_id = 0 if missing(mom_id)
rangestat (max) mom_age=age, interval(pnum mom_id mom_id) by(suid addid)
gen birth_age2=mom_age-age if !missing(mom_age)
rangestat (min) birth_age=birth_age, interval(mom_id 0 0) by(suid addid)
list, sepby(suid addid)

[Zijun Lin]

I am puzzled by the second household in which pnum is repeated.

The identifier for each household is SUID+ADDID, which means there are a total of three households here, 1-11, 2-11 and 2-12.

[Nick Cox]

That's clear now but #1 does say

SUID: household identifier

[Hemanshu Kumar]

Here is a way to do it with inbuilt Stata commands:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(suid addid) int pnum byte sex1m2f int epnmom byte age
1 11 101 1   . 39
1 11 102 2   . 38
1 11 103 1 102  5
1 11 104 1 102  3
2 11 101 2   . 55
2 11 102 2 101 20
2 12 101 2   . 40
2 12 102 1   . 35
2 12 103 2 101 10
end

preserve
    keep if !missing(epnmom)
    bysort suid addid epnmom (age): keep if _n == _N
    keep suid addid epnmom age
    rename epnmom pnum
    rename age oldest_age
    tempfile oldest
    save `oldest'
restore

merge 1:1 suid addid pnum using `oldest', keep(master match) nogen
gen birth_age = age - oldest_age if !missing(oldest_age)
drop oldest_age

which produces:

. list, sepby(suid addid) noobs abbrev(10)

  +----------------------------------------------------------+
  | suid   addid   pnum   sex1m2f   epnmom   age   birth_age |
  |----------------------------------------------------------|
  |    1      11    101         1        .    39           . |
  |    1      11    102         2        .    38          33 |
  |    1      11    103         1      102     5           . |
  |    1      11    104         1      102     3           . |
  |----------------------------------------------------------|
  |    2      11    101         2        .    55          35 |
  |    2      11    102         2      101    20           . |
  |----------------------------------------------------------|
  |    2      12    101         2        .    40          30 |
  |    2      12    102         1        .    35           . |
  |    2      12    103         2      101    10           . |
  +----------------------------------------------------------+