The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Merging data files » Merge WI to KR in Mali
Merge WI to KR in Mali [message #17896] Mon, 08 July 2019 04:42 Go to next message
sohelruhrd is currently offline  sohelruhrd
Messages: 19
Registered: July 2014
Location: Bangladesh
Member

Dear Expert,

I tried to merge the wealth index file with the KR file for several DHS surveys from African countries using Stata.

Firstly, I merged the wealth index file with HR file and then KR file with the modified HR file. It worked perfectly for most of the surveys. However, when I tried the same approach for Mali DHS 1995-96 and 2001, it didn't work. The problem appears during the merge of KR with modified HR file. Stata shows that v001 and v002 didn't uniquely match.

It will be very helpful if someone can clarify why such problem happens.

Thank you.

Regards,
Rahman
Re: Merge WI to KR in Mali [message #17898 is a reply to message #17896] Tue, 09 July 2019 01:21 Go to previous messageGo to next message
sohelruhrd is currently offline  sohelruhrd
Messages: 19
Registered: July 2014
Location: Bangladesh
Member

I tried to use the following command to merge the KR file with Wi file in Stata. This is an example for Niger DHS 1992.


************************************************************ ************************************
clear all
set more off
use "........\Niger 1998\NIWI31FL.DTA"
rename whhid hhid
merge 1:m hhid using "........\Niger 1998\NIHR31FL.DTA" // merging with household record
drop _merge
keep hhid wlthindf wlthind5 hv001 hv002 hv003
rename hv001 v001
rename hv002 v002
rename hv003 v003
sort v001 v002 v003
save "........\Niger 1998\NIHR31FLnew.DTA", replace

****
clear all
set more off
use "........\Niger 1998\NIKR31FL.DTA", clear
sort v001 v002 v003
merge m:1 v001 v002 using "........\Niger 1998\NIHR31FLnew.DTA" // merging with Wealth Index file
drop if _merge==2
drop _merge

rename wlthind5 v190 // wlthind5: wealth index
ta v190

************************************************************ ************************************************************ *********


I found the following error message:
"variables v001 v002 do not uniquely identify observations in the using data"

Thank you.

Regards,
Rahman
Re: Merge WI to KR in Mali [message #17925 is a reply to message #17898] Thu, 18 July 2019 09:42 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:

Merges involving these two surveys in Mali are difficult because they included a "sub-household" code. Fortunately, the subsequent surveys in Mali did not have this complication. (A couple of non-Mali surveys have the same problem.)

However, for what you want to do, you can bypass the HR file completely. You can merge the WI and KR files directly. It helps that the wealth index is the same for all sub-households within a household so you don't have to distinguish the sub-households. For some other merges with these surveys, you do need to do that.

The following lines show how to do this with the 1995-96 survey. You will have to change the paths. The basic strategy is to use the household id code. First you need to unpack the columns of whhid in the WI file and caseid in the KR file. I find that columns 1-12 of caseid are a match with columns 1-12 of whhid. This "unpacking" strategy will virtually always work for a merge that is giving you a problem.


* Procedure to transfer the wealth index from WI to KR without using HR

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\MLWI32FL.DTA", clear 
describe whhid
list if _n<=20, table clean


* I want to identify the separate columns of whhid
local li=1
while `li'<=12 {
gen str1 col`li'=substr(whhid,`li',1)
local li=`li'+1
}

list whhid col* if _n<20, table clean


use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\MLKR31FL.DTA", clear 
describe caseid
list caseid if _n<=20


* I want to identify the separate columns of caseid
local li=1
while `li'<=15 {
gen str1 col`li'=substr(caseid,`li',1)
local li=`li'+1
}

list caseid col* if _n<20, table clean

* It appears that columns 1-12 of caseid in the KR file match with columns 1-12 of whhid in the WI file


* Now do the merge

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\MLWI32FL.DTA", clear 
gen mergeid=whhid
sort mergeid
save e:\DHS\DHS_data\scratch\MLWItemp.dta, replace

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\MLKR31FL.DTA", clear 
gen mergeid=substr(caseid,1,12)
sort mergeid

merge mergeid using e:\DHS\DHS_data\scratch\MLWItemp.dta
tab _merge
keep if _merge==3
drop _merge

summarize wlthind*

Re: Merge WI to KR in Mali [message #17931 is a reply to message #17896] Thu, 18 July 2019 13:11 Go to previous message
boyle014 is currently offline  boyle014
Messages: 78
Registered: December 2015
Location: Minneapolis
Senior Member
You might consider using IPUMS DHS for a task like this. If you select children as your unit of analysis, the household wealth index is already available without doing any file merging. In addition, you will get a single file with all samples appended and all variables harmonized.

Professor Elizabeth Boyle
Sociology & Law, University of Minnesota, USA
Principal Investigator, IPUMS-DHS
Previous Topic: Merging BR file with PR file for NFHS-4 round(India)
Next Topic: Merging of NFHS-4 dataset
Goto Forum:
  


Current Time: Thu Mar 28 09:19:41 Coordinated Universal Time 2024