The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Dataset use in Stata » merging wealth index from Ethiopia DHS 2000 to women's file
merging wealth index from Ethiopia DHS 2000 to women's file [message #162] Wed, 20 March 2013 00:22 Go to next message
alex is currently offline  alex
Messages: 4
Registered: March 2013
Location: USA
Member
Hello!
I could not figure out how to generate the id variable for merging women's data with that of the wealth index data in Ethiopia DHS 2000. There is no explanation associated with the wealth file. I tried to convert v001, v002, and v003 into concatenated binary string combinations trying to create an id variable with resemblance to the whhid in the wealth file, but to no avail. Could anyone suggest how to create the id variable in the women's data in preparation for the merge process? I am using STATA.
Many thanks,
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #186 is a reply to message #162] Fri, 22 March 2013 13:47 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3014
Registered: February 2013
Senior Member
Attached is a response from one of our STATA experts Tom Pullum, that should answer your question.

I hope this helps.

Bridgette-DHS
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #194 is a reply to message #186] Mon, 25 March 2013 07:39 Go to previous messageGo to next message
alex is currently offline  alex
Messages: 4
Registered: March 2013
Location: USA
Member
Many thanks Bridgette,
It helps!
Alex
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #197 is a reply to message #194] Mon, 25 March 2013 08:29 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3014
Registered: February 2013
Senior Member
You are welcome.

Bridgette-DHS
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #661 is a reply to message #162] Mon, 29 July 2013 14:57 Go to previous messageGo to next message
cdt_8289 is currently offline  cdt_8289
Messages: 1
Registered: July 2013
Location: Colorado, USA
Member
I'm attempting to do something similar: merge the 2000 Ethiopia wealth index into the men's questionnaire using SPSS. I'm unfamiliar with Stata and cannot follow the previous response. Any assistance would be greatly appreciated!
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #678 is a reply to message #661] Mon, 12 August 2013 12:21 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3014
Registered: February 2013
Senior Member
Following is an example syntax for merging the wealth index with the women. You can use this, and just change the women's variable names to the men's variable names.

GET
FILE='C:\et\ETWI41FL.SAV'.

SORT CASES BY
whhid (A) .

SAVE OUTFILE='C:\et\wisort.sav'
/RENAME(WHHID=
HHID)
/COMPRESSED.

GET
FILE='C:\et\ETIR41FL.SAV'.

STRING hhid (A12).
COMPUTE hhid = SUBSTR(caseid,1,12) .
VARIABLE LABELS hhid 'hhid' .
EXECUTE .

SORT CASES BY
hhid (A) .

SAVE OUTFILE='C:\et\WOMENSORT.SAV'
/COMPRESSED.

MATCH FILES /FILE=*
/TABLE='C:\et\wisort.sav'
/BY hhid.
EXECUTE.

SAVE OUTFILE='C:\et\WI_IR.SAV'
/COMPRESSED.
COMPUTE wt = v005 / 1000000 .
EXECUTE .
WEIGHT
BY wt .
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #4080 is a reply to message #678] Thu, 26 March 2015 05:22 Go to previous messageGo to next message
Markos Mezmur is currently offline  Markos Mezmur
Messages: 1
Registered: March 2015
Location: Gaborone, Botswana
Member
Dear sir/madam,

I am trying to merge wealth index data file with individual women's data file in the 2000 Ethiopian DHS but I couldn't figure out how to do that. I also tried the syntax language posted below but I am having a difficulty to merge the files. I am using IBM SPSS version 22. any help is appreciated.

Thank you
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #4169 is a reply to message #4080] Sat, 11 April 2015 17:43 Go to previous messageGo to next message
Trevor-DHS is currently offline  Trevor-DHS
Messages: 787
Registered: January 2013
Senior Member
Try the following:
* Open wealth index data file.
get file="ETWI41FL.SAV".
* Sort cases by ID.
sort cases by WHHID.
* Save the  sorted wealth index data file.
save outfile="ETWI41FL.SAV".

* Open women's recode file.
get file="ETIR41FL.SAV".
* Create matching ID variable.
string WHHID (A12).
compute WHHID = substr(CASEID, 1, 12).

* Sort cases by ID.
sort cases by CASEID.

* Match files wealth index data to individual recode data.
match files /file=*
  /table="ETWI41FL.SAV"
  /by WHHID.
execute.

Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #13319 is a reply to message #186] Tue, 17 October 2017 15:58 Go to previous messageGo to next message
acp49
Messages: 1
Registered: October 2017
Member
I have recently used the "Merge_WI_onto_IR_do.txt" attachment posted on the "Re: merging wealth index from Ethiopia DHS 2000 to women's file" chain on 22 March 2013 to merge children's and wealth index data sets for certain countries. However, for the Burkina Faso 1998 data set, the cluster and household ID numbers in the children's data set do not line up with the numbers being reported in the "whhid" variable in the wealth index data set.

For the children's data set, the range for cluster ID (v001) is [1,210] and the range for household ID (v002) is [1,40]. For reference, the first part of the code listed in the provided attachment:

* 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)

However, the returned values from this code do not match up with these cluster and household ID ranges. I have tried parsing in several different ways. Strings c1,c2,c3,c4 are empty. Then the ranges for the remaining strings are: c5 [1,2]; c6 [0,9]; c7 [0,9]; c8 [1,7]; c9 [0,9]; c10 [0,9]; c11 [1,2]; c12 [0,9]. Therefore, c5,c6,c7 is clearly the cluster ID variable, but the remaining numbers do not match the household ID or other types of ID variables.

Would it be possible to receive further assistance with determining the relevant ID variable(s) to properly merge the children's and wealth index data sets for Burkina Faso 1998?
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #13320 is a reply to message #162] Tue, 17 October 2017 18:16 Go to previous messageGo to next message
Trevor-DHS is currently offline  Trevor-DHS
Messages: 787
Registered: January 2013
Senior Member
The wealth index file for the Burkina Faso 1998 survey does not match to v001 and v002. Instead the variables match to the variables sdepart (departement) sconcess (concession) and snumber (household number within concession). You could extract the equivalents of these variables as follows:
gen str3 wdepart=substr(whhid,5,3)
gen str3 wconcess=substr(whhid,8,3)
gen str2 wnumber=substr(whhid,11,2)
destring wdepart, gen(sdepart)
destring wconcess, gen(sconcess)
destring wnumber, gen(snumber)
drop wdepart wconcess wnumber

However, a much simpler method is just to create whhid in the children's dataset and to merge the wealth index file to the children's dataset using:
gen str12 whhid=substr(caseid,1,12)
merge m:1 whhid using BFWI31FL.dta


Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #13321 is a reply to message #13319] Wed, 18 October 2017 08:34 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3014
Registered: February 2013
Senior Member
Another response from one of our STATA experts, Tom Pullum:

If you look at hhid in BFWI31FL.dta and compare it with caseid in BFBR31FL.dta, you will see that hhid matches the first 12 (out of 15) characters in caseid. (The last 3 characters in caseid are a blank and the mother's line number). The following lines will do the merge if you change the paths to the data files. I am using an old version of the merge command, which I prefer.

use e:\DHS\DHS_data\BR_files\BFBR31FL.dta, clear
gen hhid=substr(caseid,1,12)
sort hhid
save e:\DHS\DHS_data\scratch\temp.dta, replace

use e:\DHS\DHS_data\WI_files\BFWI31FL.dta, clear
rename whhid hhid
sort hhid
merge hhid using e:\DHS\DHS_data\scratch\temp.dta
tab _merge
keep if _merge==3
drop _merge
Re: merging wealth index from Ethiopia DHS 2000 to women's file [message #14780 is a reply to message #13321] Sat, 05 May 2018 07:11 Go to previous message
Hassen
Messages: 121
Registered: April 2018
Location: Ethiopia,Africa
Senior Member
Thank you very much!!

Hassen Ali(Chief Public Health Professional Specialist)
Previous Topic: Hypertension in Nepal
Next Topic: Keeping caseid in and keeping missing observations out when using Stata "collapse"
Goto Forum:
  


Current Time: Tue Mar 19 07:39:22 Coordinated Universal Time 2024