* How to merge wealth data on ETWI41DT onto ETIR41DT
* Tom Pullum, tom.pullum@icfi.com, March 21, 2013

* First open the WI file and construct id codes for cluster and household 
*  that are compatible with those in the IR file, where they are integers.

use e:\DHS\DHS_data\WI_files\ETWI41FL.dta
describe 
list if _n<=20, table clean

* whhid has 12 characters but we need to figure out how to use them
* which part is cluster id and which part is household id?
 
gen str1 c1=substr(whhid,1,1)
gen str1 c2=substr(whhid,2,1)
gen str1 c3=substr(whhid,3,1)
gen str1 c4=substr(whhid,4,1)
gen str1 c5=substr(whhid,5,1)
gen str1 c6=substr(whhid,6,1)
gen str1 c7=substr(whhid,7,1)
gen str1 c8=substr(whhid,8,1)
gen str1 c9=substr(whhid,9,1)
gen str1 c10=substr(whhid,10,1)
gen str1 c11=substr(whhid,11,1)
gen str1 c12=substr(whhid,12,1)

list c1-c12 if _n==4, table clean

* It appears that cols 1-9 are cluster id and 10-12 are household id.
* Separate the one string into two strings.

drop c1-c12

gen str9 wv001=substr(whhid,1,9)
gen str9 wv002=substr(whhid,10,3)
list whhid wv001 wv002 if _n<=20, table clean

* Now convert those two strings into integers with the same names
*  as in the IR file.
destring wv001, gen(v001)
destring wv002, gen(v002)
drop wwhid wv001 wv002

* This is the "using" file; sort it on the merge fields.
sort v001 v002
save e:\DHS\DHS_data\WI_files\ETWI41FL_temp.dta, replace

* Now open the IR file, sort, merge, confirm, and save.
use e:\DHS\DHS_data\IR_files\ETIR41FL.dta, clear
list v000 v001 v002 v003 if _n<=20, table clean
sort v001 v002
merge using e:\DHS\DHS_data\WI_files\ETWI41FL_temp.dta
tab _merge
drop _merge
list v000 v001 v002 v003 wlthind* if _n<=20, table clean
list v000 v001 v002 v003 wlthind* if _n<=20, table clean nolabel
save e:\DHS\DHS_data\IR_files\ETIR41FL_WI.dta, replace