The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Dataset use in Stata » Merging Birth's / Chidren's with Household Member Recode
Merging Birth's / Chidren's with Household Member Recode [message #3851] Wed, 25 February 2015 02:12 Go to next message
karlo
Messages: 9
Registered: December 2014
Member
Hello!

I'm having a little difficulty merging the Birth's and Children's Recode with the Household Member Recode (I need both individual and household variables). I can easily merge the individual with the household member recode but is having a hard time for births/children.

I am interested particularly in using individual and household data for ANC and Delivery location (Births + Household member recode) and immunization history of children aged 1-2 years from the last interview (Children + household member recode).

I'm using the variables v001 v002 v003 (hv001 hv002 hvidx) (as with the individual -> household recode) but could not merge the data sets =/ I used http://dhsprogram.com/data/Merging-Datasets.cfm as a reference but STATA is giving me results saying that the entries do not match.

Thanks for your help!


Karlo

[Updated on: Wed, 25 February 2015 20:59]

Report message to a moderator

Re: Merging Birth's / Chidren's with Household Member Recode [message #3899 is a reply to message #3851] Wed, 04 March 2015 09:28 Go to previous messageGo to next message
Trevor-DHS is currently offline  Trevor-DHS
Messages: 800
Registered: January 2013
Senior Member
Please post your code for matching and we can then review it.
Re: Merging Birth's / Chidren's with Household Member Recode [message #8687 is a reply to message #3899] Wed, 02 December 2015 08:12 Go to previous messageGo to next message
karlo
Messages: 9
Registered: December 2014
Member
Hello!

Here's the code that I used:

merge 1:m v001 v002 using "PHPR62FL.DTA"

Is this correct?

Before the merging, I sorted the v001 and v002 as I usually do.

Sorry for the late reply to this message. I still need to merge the files though =(
Re: Merging Birth's / Chidren's with Household Member Recode [message #8702 is a reply to message #8687] Thu, 03 December 2015 10:56 Go to previous messageGo to next message
Trevor-DHS is currently offline  Trevor-DHS
Messages: 800
Registered: January 2013
Senior Member
You need to include the line number of the person in the merge. For the PR file that is in hvidx and for the BR or KR file it is in b16 (line number of child in household). Note that there are some children in the BR and KR files for whom b16 is 0, 99, or missing (.), because the child in the BR file either does not live in the household or is dead or had missing data - it is best to drop these before merging. Then perform a 1:1 merge to merge the data.
Re: Merging Birth's / Chidren's with Household Member Recode [message #8710 is a reply to message #8702] Fri, 04 December 2015 01:24 Go to previous messageGo to next message
karlo
Messages: 9
Registered: December 2014
Member
Hey trevor!

Thanks a lot for the support. Finally able to merge the file.
Re: Merging Birth's / Chidren's with Household Member Recode [message #9317 is a reply to message #8702] Wed, 09 March 2016 16:29 Go to previous messageGo to next message
marron is currently offline  marron
Messages: 1
Registered: March 2016
Member
Hi, I have a follow-up question with regards to your suggestion to use the b16 variable in the birth recode file. I am unable to locate the b16 variable in the DHS surveys from Phase III and earlier, at least for countries in southern Africa. Does this mean that it is impossible to merge the birth recode file with the household file for these earlier surveys?
Re: Merging Birth's / Chidren's with Household Member Recode [message #9321 is a reply to message #9317] Thu, 10 March 2016 07:42 Go to previous messageGo to next message
Trevor-DHS is currently offline  Trevor-DHS
Messages: 800
Registered: January 2013
Senior Member
Yes, it does mean that you can't link the birth history with the household roster. We added b16 into the surveys specifically to allow this.
Re: Merging Birth's / Chidren's with Household Member Recode [message #10940 is a reply to message #9321] Mon, 10 October 2016 10:50 Go to previous messageGo to next message
sammyladele is currently offline  sammyladele
Messages: 11
Registered: June 2015
Location: Nigeria
Member

Hello Trevor, I also have a question. What if I'm merging CR with KR does it follow the same procedure using b16?

Oladele Rotimi
Re: Merging Birth's / Chidren's with Household Member Recode [message #10941 is a reply to message #10940] Mon, 10 October 2016 11:13 Go to previous messageGo to next message
Trevor-DHS is currently offline  Trevor-DHS
Messages: 800
Registered: January 2013
Senior Member
The CR file is the couples recode file. The KR is the children under under five recode file (sometimes known as the kids recode file). B16 is the children's line number from the household schedule and is needed when merging the children's data from the KR file with their information from the household schedule (PR file). The children's line number is not needed for merging with the CR file - you would merge based on the cluster (v001 <-> mv001), household (v002 <-> mv002), and the husband's line number from the women's recode (v034) with the respondent's line number from the men's data (v003).
Re: Merging Birth's / Chidren's with Household Member Recode [message #10942 is a reply to message #10941] Mon, 10 October 2016 11:18 Go to previous messageGo to next message
sammyladele is currently offline  sammyladele
Messages: 11
Registered: June 2015
Location: Nigeria
Member

Thanks Trevor, I will try it now.

Oladele Rotimi
Re: Merging Birth's / Chidren's with Household Member Recode [message #15108 is a reply to message #10942] Mon, 04 June 2018 03:52 Go to previous messageGo to next message
anikhpg42@gmail.com is currently offline  anikhpg42@gmail.com
Messages: 38
Registered: December 2017
Location: Bangladesh
Member

Hi Trevor,
I just want to know that, what does the variable 'Line number of the household member' (hvidx) means minutely?
That is, if hvidx = 1, then what does it indicate?


ASIBUL ISLAM ANIK

[Updated on: Mon, 04 June 2018 03:59]

Report message to a moderator

Re: Merging Birth's / Chidren's with Household Member Recode [message #28815 is a reply to message #15108] Wed, 13 March 2024 06:32 Go to previous messageGo to next message
Joyeuse
Messages: 1
Registered: March 2024
Member
Hi Trevor,

I have a question regarding merging PR and KR recodes. I am working with the Rwanda DHS 2019/20 dataset. My focus is on two aspects: household living arrangements (available in the PR recode) and child health indicators (available in the KR recode).

For the living arrangements, I utilized the STATA codes provided by DHS, which are accessible in the DHS indicators. I created a sub-sample based on these codes and subsequently merged it with the original PR recode.

Following this, I merged the newly created dataset with the KR recode. However, at the end of the merging process, I noticed that I am losing one category.

Could you please advise on how to resolve this issue?

Thank you

. tab cores_type

cores_type | Freq. Percent Cum.
---------------------------+-------------------------------- ---
Living with both parents | 16,982 63.60 63.60
With mother, not father | 6,046 22.64 86.24
With father, not mother | 617 2.31 88.55
Living with neither parent | 3,056 11.45 100.00
---------------------------+-------------------------------- ---
Total | 26,701 100.00


and I have this after final merging


tab cores_type

cores_type | Freq. Percent Cum.
---------------------------+-------------------------------- ---
Living with both parents | 5,620 75.05 75.05
With mother, not father | 1,866 24.92 99.97
Living with neither parent | 2 0.03 100.00
---------------------------+-------------------------------- ---
Total | 7,488 100.00



these are the codes I used

***preparation of household member recod (PR)***
use "C:\Users\pc\Desktop\Rwanda_DHS_Data\RWPR81DT_Household members recode\RWPR81FL.DTA",clear

rename hvidx c_line
rename hv001 cluster_line
rename hv002 hh_line
order c_line cluster_line hh_line
save "C:\Users\pc\Desktop\Rwanda_DHS_Data\PR.dta", replace

***preparation of children recod (KR)***
use "C:\Users\pc\Desktop\Rwanda_DHS_Data\RWKR81DT_Children recode\RWKR81FL.DTA", clear

tab b16
drop if b16==.
drop if b16==0

rename b16 c_line
rename v001 cluster_line
rename v002 hh_line
order c_line cluster_line hh_line
save "C:\Users\pc\Desktop\Rwanda_DHS_Data\KR.dta", replace

****Creation of a sub_sample of variables from PR datset to create ** Living arrangements ***

* Preparing files to produce the indicators, this required several merges
use "C:\Users\pc\Desktop\Rwanda_DHS_Data\RWPR81DT_Household members recode\RWPR81FL.DTA", replace
keep hv001 hv002 hvidx hv005 hv009 hv024 hv025 hv101-hv105 hv111-hv114 hv270 hc60
save PR_temp.dta, replace

* Prepare a file of potential mothers
use PR_temp.dta, clear
drop if hv104==1
drop if hv105<15
keep hv001 hv002 hvidx hv102
gen in_mothers=1
rename hv102 hv102_mo
rename hvidx hv112
sort hv001 hv002 hv112
save PR_temp_mothers.dta, replace

* Prepare a file of potential fathers
use PR_temp.dta, clear
drop if hv104==2
drop if hv105<15
keep hv001 hv002 hvidx hv102
gen in_fathers=1
rename hv102 hv102_fa
rename hvidx hv114
sort hv001 hv002 hv114
save PR_temp_fathers.dta, replace

* Prepare file of children for merges
use PR_temp.dta, clear
drop if hv102==0
drop if hv105>17
gen in_children=1

* Merge children with potential mothers
sort hv001 hv002 hv112
merge hv001 hv002 hv112 using PR_temp_mothers.dta
rename _merge _merge_child_mother

* Merge children with potential fathers
sort hv001 hv002 hv114
merge hv001 hv002 hv114 using PR_temp_fathers.dta
rename _merge _merge_child_father
gen hv112r=hv112
gen hv114r=hv114

* Code 99 of the mother or father is not de jure
replace hv112r=99 if hv112>0 & hv102_mo==0
replace hv114r=99 if hv114>0 & hv102_fa==0
keep if in_children==1
drop in_* _merge*
label define HV112R 0 "Mother not in household" 99 "In hh but not de jure"
label define HV114R 0 "Father not in household" 99 "In hh but not de jure"
label values hv112r HV112R
label values hv114r HV114R
tab1 hv112r hv114r

gen cores_type=.
replace cores_type=1 if (hv112r>0 & hv112r<99) & (hv114r>0 & hv114r<99)
replace cores_type=2 if (hv112r>0 & hv112r<99) & (hv114r==0 | hv114r==99)
replace cores_type=3 if (hv112r==0 | hv112r==99) & (hv114r>0 & hv114r<99)
replace cores_type=4 if (hv112r==0 | hv112r==99) & (hv114r==0 | hv114r==99)
label define cores_type 1 "Living with both parents" 2 "With mother, not father" 3 "With father, not mother" 4 "Living with neither parent"
label values cores_type cores_type
tab cores_type


***preparation for merging with PR recode**
rename hvidx c_line
rename hv001 cluster_line
rename hv002 hh_line
order c_line cluster_line hh_line


***FIRST MERGE: LIVING ARRANGEMENT + PR RECODE*****
merge 1:1 c_line cluster_line hh_line using "C:\Users\pc\Desktop\Rwanda_DHS_Data\PR.dta", keep(match)
drop _merge
tab cores_type
save "C:\Users\pc\Desktop\Rwanda_DHS_Data\RWPR81DT_Household members recode\PR_living arrangement.dta", replace

***SECOND MERGE: ABOVE DATASET + CHILDREN RECODE***

merge 1:1 c_line cluster_line hh_line using "C:\Users\pc\Desktop\Rwanda_DHS_Data\KR.dta", keep(match)
drop _merge

save "C:\Users\pc\Desktop\Rwanda_DHS_Data\RWPR81DT_Household members recode\Joy_Final dataset_DHS_Project.dta", replace
Re: Merging Birth's / Chidren's with Household Member Recode [message #28838 is a reply to message #28815] Mon, 18 March 2024 07:22 Go to previous message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3113
Registered: February 2013
Senior Member

Following is a response from Senior DHS staff member, Tom Pullum:

When you do this kind of merge, a variable called "_merge" is constructed with 3 categories. The category you are most interested in is _merge=3, which identifies children that are in both files. However, there are two other categories, "_merge=1" and "_merge=2". The codes 1 and 2 depend on which file is the "master" file, the one you start with, and which file is the "using" file, the one named after the word "using" in the merge command.

There are many cases in the PR file that are not in the KR file. These are household members above the age of 5, as well as children under 5 who were present in the household but whose mothers were not present, so they do not appear in a birth history. You almost certainly want to drop these cases.

There are relatively few cases in the KR file that are not in the PR file. These are children with b16=., who have died, and children with b16=0, who are living but are living elsewhere. In order to do the merge, you probably removed them already.

If you just enter "keep if _merge==3" and "drop _merge" you will then have the cases you want. I hope this is what you were asking about.


Previous Topic: Measles vaccination NFHS 5 India
Next Topic: DHS 5 India dataset IR file-r900 error
Goto Forum:
  


Current Time: Sun Jul 14 14:04:27 Coordinated Universal Time 2024