* Example of merging child age 0-17 with mother and father in same household.
  
* Only variables kept other than id are hv106 for child, v106 for mother, and mv106 for father

cd e:\DHS\DHS_data\scratch

* Prepare file of potential mothers
use v001 v002 v003 v106 using "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\CMIR71FL.DTA", clear 
* Specify which of the mother's variables to keep.  Illustrative only
*keep v001 v002 v003 v106
rename v001 hv001
rename v002 hv002
rename v003 mo_line
gen mother=1
sort hv001 hv002 mo_line
save mo_temp.dta, replace

* Prepare file of potential fathers
use mv001 mv002 mv003 mv106 using "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\CMMR71FL.DTA", clear
* Specify which of the father's variables to keep.  Illustrative only
*keep mv001 mv002 mv003 mv106
rename mv001 hv001
rename mv002 hv002
rename mv003 fa_line
gen father=1
sort hv001 hv002 fa_line
save fa_temp.dta, replace

* Prepare file of children 
use hv001 hv002 hvidx hv112 hv114 hv105 hv106 using "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\CMPR71FL.DTA", clear
keep if hv105<=17
* Specify which child variables to keep.  Illustrative only.  Must keep hv112 and hv114
*keep hvidx hv112 hv114 hv106
gen mo_line=hv112
gen mother_is_in_hh=0
replace mother_is_in_hh=1 if mo_line>0 & mo_line<. 
gen fa_line=hv114
gen father_is_in_hh=0
replace father_is_in_hh=1 if fa_line>0 & fa_line<. 
gen child=1
tab hv106

* merge with mother
sort hv001 hv002 mo_line
merge hv001 hv002 mo_line using mo_temp.dta
rename _merge mo_merge

* merge with father
sort hv001 hv002 fa_line
merge hv001 hv002 fa_line using fa_temp.dta
rename _merge fa_merge

* Clean up
drop if child=.
tab hv106

tab v106 mv106,m