* How to merge the KR and HW files from the Egypt 2000 DHS survey

set more off

* Prepare the KR file
use e:\DHS\DHS_data\KR_files\EGKR42FL.dta, clear

* caseid is left-adjusted with no leading blanks, but has trailing blanks;
* caseid is str15, but longest is str13.  It should be hhid plus 3 cols for v003

* I must convert it to be right-adjusted and without the woman's line number

use e:\DHS\DHS_data\KR_files\EGKR42FL.dta, clear
codebook caseid
recast str13 caseid

gen length=strlen(caseid)
tab length

* remove the last three non-missing columns of caseid, which are the line number of the woman, to get hhid
gen str2 blank2="  "
gen str3 blank3="   "
gen str4 blank4="    "
gen str12 caseidrev="            "
replace caseidrev=blank4+substr(caseid,1,8)  if length==11
replace caseidrev=blank3+substr(caseid,1,9)  if length==12
replace caseidrev=blank2+substr(caseid,1,10) if length==13

rename caseidrev hhid
rename b16 hvidx
sort hhid hvidx

save e:\DHS\scratch\temp.dta, replace


* Prepare the HW file and do the merge
use e:\DHS\DHS_data\HW_files\EGHW41FL.dta, clear

* hwhhid is right adjusted with no trailing blanks, but has leading blanks, as is normal
* hwhhid is str12
rename hwhhid hhid
rename hwline hvidx
sort hhid hvidx
merge hhid hvidx using e:\DHS\scratch\temp.dta

tab _merge 

* then keep if _merge==3, drop _merge, and save the file

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\EGHW41FL.DTA" 
rename hwline b16

local li=1
while `li'<=12 {
gen col`li'=substr(hwhhid,`li',1)
local li=`li'+1
}

list hwhhid col* if _n<=20, table clean

drop col*
gen v001=substr(hwhhid,3,7)
gen v002=substr(hwhhid,10,3)

destring v001 v002, replace
sort v001 v002 b16
save e:\DHS\DHS_data\scratch\EGtemp.dta, replace

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\EGKR42FL.DTA" 
drop if b16==0 | b16==.
sort v001 v002 b16

merge v001 v002 b16 using e:\DHS\DHS_data\scratch\EGtemp.dta
tab _merge