Hi:

I am working with a dataset that looks something like this:

HHID PID MID Rel_Head Age
10 1 3 1 56
10 2 . 2 48
10 3 . 7 75
10 4 . 8 80
10 5 2 6 18
10 6 2 6 16
10 7 3 5 52
10 8 3 5 49
12 1 . 1 25
12 2 . 2 24


where HHID is household identifier; PID is member identifier within each household; MID is the identifier for the mother; Rel_Head is the relationship to the head of the household (it's 1 if individual is head; 2 if they are spouse of head; 6 if child of head; 8 if father of head and so on).

For instance, in the above dataset, PID-5 and 6 are children of 1 and 2 in household 10.

I want to construct two variables from this dataset:

1. The number of children for each mother in the household;
2. The age at first birth for each mother. In other words, difference between her age and her oldest offspring's age.

Basically, I want to have two variables corresponding to the last two columns below:

HHID PID MID Rel_Head Age N_children Age_f_birth
10 1 3 1 56
10 2 . 2 48 2 30
10 3 . 7 75 2 19
10 4 . 8 80
10 5 2 6 18
10 6 2 6 16
10 7 3 5 52
10 8 3 5 49
12 1 . 1 25
12 2 . 2 24



Any help would be immensely appreciated! Thank you!