Hi everyone,

So I'm using Stata 12.0 and currently working on a dataset with around 500,000 indivs, 7000 of these idivs are "enrolled". I want to determine for my enrolled indivs, how many idivs out of the whole pop share a house id with the enrolled idiv at a specific date.
The dataset is setup in a wide format, each indiv has a houseid1 with start and end dates of the residency, if they moved then they have a second house id (houseid2) with start and end dates for that as well, and so on..
I've determined the enrolled houseid's (houseid_enrolled) and the date of interest and want to loop through each of the variables list (houseid1, houseid2, houseid3...) for all the participants to determine how many people lived at the enrolled house on the specific date. For some indivs this could be their houseid1 or houseid2 and so on, below is the code I've gotten so far currently the count is not adding up and just counting 1 for each enrolled houseid.
My plan was to then sum the num_house*_members for each enrolled indiv after to get the total living at one house during the time, if there is a cleaner method that would also be appreciated.
I'm aware I could just create separate datasets and merge on the houseid variables and count them that way but Im looking to learn how to do this in a more efficient way.

Thanks, dont forgot to wash your hands!
Ben

code so far
Code:
gen num_house1_members=0
gen num_house2_members=0
gen num_house3_members=0
forvalues i=1/3 {
foreach v of var houseid`i'{
    replace num_house`i'_members=[num_house`i'_members+1] if `v'==houseid_enrol  &  inrange(date,strtdate`i',enddate`i')
    }    
}
Dataset

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float obs double houseid_enrol float date double(houseid1 houseid2) float(strtdate1 enddate1)
293437           . 21527           .           . 21191     .
258630           . 21675           .           .     .     .
248761           .     .           .           .     .     .
153093           .     . 64006010101           . 18628     .
411306           .     . 64006010101           . 20287 21143
163275           . 21682 64006006901           .     .     .
422822           . 21724 64006010101           .     .     .
     1 64006010101 21836 64006010101           . 21653     .
106823           .     . 64006010201           . 18628 20691
407350           .     . 64006010201           . 18628     .
360936           .     . 64006010201           . 19486     .
176415           .     . 64006010201           . 20381     .
305027           .     . 64006010201           . 20781     .
263907           .     . 64006010201           . 21864     .
     2 64006010201 21906 64006010201           .     .     .
 57961           .     . 64006010301           . 18628 18999
 36251           .     . 64006010301           . 18628     .
 65297           .     . 64006010501           . 20792     .
 94142           .     . 64006010501           . 21246     .
end
format %td date
format %tddd_Mon_CCYY strtdate1
format %tddd_Mon_CCYY enddate1