Count number of alive children within household

[Ursula Npisen]

I am trying to compare variables to different variables of different observations within a group - and I am not sure if this is even possible in Stata.

I would like to count the number of alive siblings alive at age 5 (or alternatively the age of the death) of the observed child.

From the data, I have been able to generate the first four columns, but need to construct a column along the lines of column 5 here.

HH //// Child //// Age at Survey ////Age at Death //// Number of siblings alive at age 5(/at age of death)
1 //////// 1 //////////// 18 //////////////////////////// 4 //////////////////// 1
1 //////// 2 //////////// 15 //////////////////////////// - //////////////////// 1
1 //////// 3 //////////// 11 //////////////////////////// - //////////////////// 2
1 //////// 4 //////////// 8 //////////////////////////// - //////////////////// 2
2 //////// 1 //////////// 4
...

(Age at survey for those who have died simply means the age they would be today)

Can anyone help?

[William Lisowski]

Welcome to Statalist.

I may not have time to answer your question before someone else does, but I have taken the time to change your data into something useful in Stata for developing and testing code, and I post it here for others to make use of.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(hh child agesurvey agedeath number)
1 1 18 4 1
1 2 15 . 1
1 3 11 . 2
1 4  8 . 2
end

[William Lisowski]

I have a solution, but I am not happy with it because it involves looping over the observations in the dataset, something that we usually try to avoid in Stata. I feel that I'm overlooking something, and and hope someone else will show a better approach.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(hh child agesurvey agedeath number)
1 1 18 4 1
1 2 15 . 1
1 3 11 . 2
1 4  8 . 2
end

sort hh child
generate yearborn = -agesurvey
generate yearlast = min(0,-agesurvey+agedeath)
generate year5    = min(yearlast,-agesurvey+5)

generate count = 0
generate temp = 0
forvalues o = 1/`c(N)' {
    quietly replace temp = sum( hh==hh[`o'] & child!=child[`o'] & inrange(year5[`o'],yearborn,yearlast) )
    quietly replace count = temp[_N] in `o'
    }
drop temp
list, clean noobs abbreviate(16)

. list, clean noobs abbreviate(16)

    hh   child   agesurvey   agedeath   number   yearborn   yearlast   year5   count  
     1       1          18          4        1        -18        -14     -14       1  
     1       2          15          .        1        -15          0     -10       1  
     1       3          11          .        2        -11          0      -6       2  
     1       4           8          .        2         -8          0      -3       2