clear all
cd data
use observation
sort observeid
save, replace
use householdinfo
sort observeid
merge m:1 observeid using observation, keepusing(obs_entry_date)
drop round
keep if _merge ==3
drop if socialgpid ==""
gen year = year( obs_entry_date/86400000)
drop if year < 2004

gen round = year
sort socialgpid round
duplicates drop socialgpid round, force
gen period = round[_n+1]-round
replace period = 2014-round if socialgpid!=socialgpid[_n+1]
expand period
replace round = round-2004
sort socialgpid round
by socialgpid round: gen p= _n-1
replace round = round + p
drop if round > 10
drop  period p _merge  obs_entry_date
tostring round, generate(rounds)
replace rounds = "0" + rounds if strlen(rounds) == 1
gen socialkey = socialgpid+  rounds
drop hhinforid 
sort socialkey
duplicates drop socialkey, force
save rYHouseholdInfo, replace