use observation
sort observeid
save observation, replace
use householdInfo
drop if socialgpid == ""
sort observeid
merge observeid using observation, nokeep keep (obs_de_date)
gen refdate = $refdate  + 180
drop if refdate < obs_de_date
gen period = qofd(refdate )-qofd(obs_de_date ) +1
expand period 
sort socialgpid obs_de_date
by socialgpid obs_de_date: gen p= _n-1
gen quarterdate = qofd(obs_de_date) + p
gen refquarter = qofd(refdate )
sort socialgpid quarterdate
egen maxobs_de_date = max(obs_de_date), by(socialgpid quarterdate)
keep if obs_de_date == maxobs_de_date 
tostring quarterdate, gen(key)
replace key = socialgpid + key
sort key
save householdinfortemp, replace
keep if refquarter == quarterdate 
format refdate %td
drop _merge period quarterdate refquarter hhinforid observeid  p
duplicates drop socialgpid, force
sort socialgpid
save cross\crosshouseholdinfo, replace
global refmonth = month( $refdate  + 180)
global refyear = year( $refdate  + 180)
save cross\crosshouseholdinfo$refmonth$refyear, replace
use cross\crosshouseresidency
merge socialgpid using cross\crosshouseholdinfo
keep if _merge ==3
save cross\crossresidencyhouseholdinfo, replace