Merging Birth's / Chidren's with Household Member Recode [message #3851] |
Wed, 25 February 2015 02:12 ![Go to next message Go to next message](/theme/default/images/down.png) |
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 #28815 is a reply to message #15108] |
Wed, 13 March 2024 06:32 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
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 Go to previous message](/theme/default/images/up.png) |
Bridgette-DHS
Messages: 3151 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.
|
|
|