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
Merging Egypt 2000 KR and HW files [message #3583] Tue, 13 January 2015 15:06 Go to next message
rejone4@emory.edu is currently offline  rejone4@emory.edu
Messages: 8
Registered: November 2014
Location: United States
Member

Hello,
I am trying to merge the KR and HW files from Egypt's DHS 2000 files. I have used this code for other years with the same variables and have had no problems. However
when I run the code below for these two files the code runs but then there are no observations.


*Create the household ID by dropping the 3 d character line number from caseid
gen hwhhid = substr(caseid,1,length(caseid)-3)

*make a copy of the household line number
clonevar hwline = b16

*Sort on the household ID and the line number from the household schedule
sort hwhhid hwline

*merge the data

merge m:1 hwhhid hwline using "H:\Thesis\Egypt_2008\Egypt_2000(IV)\EGHW01FL.dta"

Re: Merging Egypt 2000 KR and HW files [message #3660 is a reply to message #3583] Fri, 23 January 2015 11:25 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3016
Registered: February 2013
Senior Member
See post #3219 for an example.
Re: Merging Egypt 2000 KR and HW files [message #11523 is a reply to message #3583] Sun, 08 January 2017 10:24 Go to previous messageGo to next message
r01efa16
Messages: 10
Registered: November 2016
Location: UK
Member

Greetings

I have the same propblem. I could not merge the children with their Height and Weight measurements for 2000 and 2005 waves. Even if I could successfully merge the datasets for 1992 and 1995

Could you tell me If you could find any solution ?!!

Your response is highly appreciated.
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 next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3016
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



Re: Merging Egypt 2000 KR and HW files [message #20145 is a reply to message #11541] Mon, 28 September 2020 18:24 Go to previous messageGo to next message
id709nvz is currently offline  id709nvz
Messages: 43
Registered: March 2019
Member
Dear,

I follow the same code IN ORDER to merge ETHIOPIAN height/weight HW with Ethiopian 2000 dhs and also with dhs 2005, but without success. Height/Weight based on the new WHO growth standard is collected for children at a household level.

Could you please help me out with STATA code that will do the merge? HW with KR files for 2000 and 2005 dhs.

thanks for your help in advance!
Kind regards!
Re: Merging Egypt 2000 KR and HW files [message #20168 is a reply to message #20145] Mon, 05 October 2020 07:00 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3016
Registered: February 2013
Senior Member

Following is a response from Senior DHS Stata Specialist, Tom Pullum:

The tricky thing about these merges is that the HW files have the household id as a string. With "describe" you can find that hwhhid is a 12-character string. You then need to extract v001 and v002 from hwhhid. Below I show a trick to help find out which columns are v001 and which are v002. You use destring and substring. I show how to do it with the 2000 survey but the alignment is the same in the 2005 survey.

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
Re: Merging Egypt 2000 KR and HW files [message #20170 is a reply to message #20168] Mon, 05 October 2020 09:38 Go to previous messageGo to next message
id709nvz is currently offline  id709nvz
Messages: 43
Registered: March 2019
Member
Dear,
Thanks for your reply. I am sorry, but there is a misunderstanding on my question.
When I say HW I meant height and weight measurement. So to clarify, I want to merge the height and weight index with KR file of Ethiopian DHS for 2000 and 2005.

I look forward to your valuable suggestions.
Re: Merging Egypt 2000 KR and HW files [message #20172 is a reply to message #20168] Mon, 05 October 2020 10:21 Go to previous messageGo to next message
id709nvz is currently offline  id709nvz
Messages: 43
Registered: March 2019
Member
Dear,
I am really sorry. Your answer is exactly what I want, sorry for my misunderstanding..

I just used the code and it worked perfect. I have some technical issues that I believe you can clear it to me.
My confusion is:

The variable name for Height/weight index for Ethiopian DHS 2000 AND DHS 2005 is HC70, HC71 etc and for the rest DHS 2011 AND DHS 2016 is HW70, HW71 etc.
Since I want to combine all the survey (2000, 2005, and 2011, 2016) together, will it be problematic if I rename and label variables to make it consistent across survey?

Please let me know your valuable suggestions.
Kind regards
Re: Merging Egypt 2000 KR and HW files [message #20198 is a reply to message #20172] Wed, 07 October 2020 07:51 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3016
Registered: February 2013
Senior Member

Following is a response from DHS Research & Data Analysis Director, Tom Pullum:

DHS has some naming rules that can be confusing. In the PR file, some variables (such as anthropometry) have the prefix hc for children, ha for women, and (if included) hb for men. In the IR, KR, and BR files the prefix for children is hw. If you find hc70 in the PR file and hw70 in the KR file, they are the same variable, with the same values for the same child. The only difference is that there will be some children in the PR file who are not in the KR file (because their mother was not in the household), so there are a few more cases with hc70 than with hw70. The point is that it's the same variable, just in a different file. So when you merge the HW file with the PR file, the prefix should be hc, and when you merge with the KR file it should be hw. But it's a minor matter, just a naming convention.

Re: Merging Egypt 2000 KR and HW files [message #20201 is a reply to message #20198] Wed, 07 October 2020 09:48 Go to previous message
id709nvz is currently offline  id709nvz
Messages: 43
Registered: March 2019
Member
Dear,

Thanks for your information. I took note of that.

Kind regards!
Previous Topic: Merging IR and MR files India
Next Topic: Linking child data with parentals information
Goto Forum:
  


Current Time: Thu Mar 28 13:05:05 Coordinated Universal Time 2024