The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Merging data files » Merging Egypt 2000 KR and HW files
Re: Merging Egypt 2000 KR and HW files [message #11541 is a reply to message #11523] Mon, 09 January 2017 21:43 Go to previous messageGo to previous message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3204
Registered: February 2013
Senior Member
Following is a response from Senior DHS Stata Specialist, Tom Pullum:

Quote:
This merge is non-standard because caseid in the KR file for this survey is left-adjusted and has no leading blanks. You have to move the trailing blanks to the beginning, and remove the woman's line number in order to get a hhid that will match with the HW file. This was a mistake but unfortunately it comes up in a few other surveys too. To figure this out I had to do quite a bit of column-by-column deciphering of caseid. The following lines will work. You have to change the paths. I am using an older version of the merge command, which I much prefer to the current version.


* 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



 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Merging IR and MR files India
Next Topic: Linking child data with parentals information
Goto Forum:
  


Current Time: Mon Dec 9 07:52:10 Coordinated Universal Time 2024