The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Merging data files » Merging KR with PR (Merging Nigeria 2018 KR(master) with PR(using))
Merging KR with PR [message #25445] Thu, 20 October 2022 14:56 Go to next message
kehinde.atoloye@gmail.com is currently offline  kehinde.atoloye@gmail.com
Messages: 3
Registered: October 2022
Member
Country: Nigeria
Survey:2018
Dataset: Child Recode (KR) and Household Member Recode(PR)

I am working on anemia, diarrhea, and malaria in under-5 children in Nigeria. The data on anemia and diarrhea is contained in the KR file while the data on malaria is contained in the PR file(I do not need the fever data in the KR file). The issue is how to merge the "HOUSEHOLD - Malaria: by Household Member" subset of the PR file to the KR file. I have adapted several codes I sourced on this platform but to no avail. I keep getting no observation after merging. I believe I can match the children in the KR file with the "Malaria: by Household Member" section of PR. In the PR file, I tabulated the "child's age in months" against "final result of malaria from blood smear test" and below is a sample of the output as evidence that children's malaria test results are in the PR file. I will appreciate any assistance in this regard to be able to match the children in KR with children in PR. Thanks.


| final result of
child's | malaria from blood
age in | smear test
months | negative positive | Total
-----------+----------------------+----------
6 | 141 31 | 172
7 | 114 34 | 148
8 | 128 23 | 151
9 | 129 21 | 150
10 | 100 21 | 121
11 | 132 16 | 148
12 | 159 24 | 183
13 | 164 39 | 203
14 | 129 25 | 154
15 | 148 35 | 183
16 | 116 32 | 148
17 | 130 28 | 158
18 | 134 24 | 158
19 | 110 21 | 131
20 | 109 32 | 141



Below is the most recent code I adapted:

use "C:\Users\ezpawn\Downloads\NG_2018_DHS_10132022_1330_145124\NGPR7BFL.DTA", clear
keep if  hmhidx==1
gen cluster=hv001
gen hh=hv002
gen caretaker= hml30
sort cluster hh caretaker
save "C:\Users\ezpawn\Downloads\NG_2018_DHS_10132022_1330_145124\NGPR7BFL_Temp.DTA", replace

use "C:\Users\ezpawn\Downloads\NG_2018_DHS_10132022_1330_145124\NGKR7BFL.dta", clear
gen cluster=v001
gen hh=v002
gen caretaker=v003
sort cluster hh caretaker
merge cluster hh caretaker using "C:\Users\ezpawn\Downloads\NG_2018_DHS_10132022_1330_145124\NGPR7BFL_Temp.DTA"

tab _merge

keep if _merge==3
save "C:\Users\ezpawn\Downloads\NG_2018_DHS_10132022_1330_145124\NG_KR_PR_Merged.dta", replace

gen children_per_caretaker=1
collapse(sum) children_per_caretaker, by(cluster hh caretaker)

tab children


Re: Merging KR with PR [message #25450 is a reply to message #25445] Fri, 21 October 2022 08:56 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 2670
Registered: February 2013
Senior Member
Following is a response from DHS staff member, Tom Pullum:


I believe the Stata lines pasted below will do what you want. You did not need to use the line for the child's caretaker. Every member of a household, including every child, has their own unique line number and line of data. In the PR file, hvidx is the line number, and in the KR file, b16 is the line number (for children who are alive and living with the mother). I'm not sure which variables you want, but these lines keep all the hml variables in the PR file and all the ml variables in the KR file. You could include other variables. It appears that there was subsampling for the malaria tests. You may want to modify the "keep" line at the end. Good luck!

* Specify workspace
cd e:\DHS\DHS_data\scratch

* Nigeria 2018
* Combine hml variables in the PR file with the ml variables in the KR file

use "...NGPR7BFL.DTA", clear 
keep if hc1<.
rename hv001 cluster
rename hv002 hh
rename hvidx line
keep cluster hh line hml*
sort cluster hh line
save NGPRtemp.dta, replace

use "...NGKR7BFL.DTA", clear 
keep if b16>0 & b16<.
rename v001 cluster
rename v002 hh
rename b16 line
keep cluster hh line v0* ml* hw*
sort cluster hh line
merge cluster hh line using NGPRtemp.dta

tab hml32 _merge,m

keep if _merge==5 & hml32<.
drop _merge

[Updated on: Fri, 21 October 2022 08:57]

Report message to a moderator

Re: Merging KR with PR [message #25451 is a reply to message #25450] Fri, 21 October 2022 11:30 Go to previous message
kehinde.atoloye@gmail.com is currently offline  kehinde.atoloye@gmail.com
Messages: 3
Registered: October 2022
Member
Thank you. It worked but I had to change the last keep line from keep if _merge==5 & hml32<. to keep if hw1<.. I do not understand why the former drops all observations of hml32. Thanks.


Afterthought: I observed that it also keeps the hml32 observations if I use keep if hml32<. instead of keep if _merge==5 & hml32<.

It will be interesting to know what the condition _merge==5 does. Thanks a million.

* Specify workspace
cd e:\DHS\DHS_data\scratch

* Nigeria 2018
* Combine hml variables in the PR file with the ml variables in the KR file

use "NGPR7BFL.DTA", clear 
keep if hc1<.
rename hv001 cluster
rename hv002 hh
rename hvidx line
keep cluster hh line hml32
sort cluster hh line
save NGPRtemp.dta, replace

use "NGKR7BFL.DTA", clear 
keep if b16>0 & b16<.
rename v001 cluster
rename v002 hh
rename b16 line
keep cluster hh line v0* ml* hw*
sort cluster hh line
merge cluster hh line using NGPRtemp.dta

tab hml32 _merge,m

// keep if _merge==3 & hml32<.

keep if hw1<.
drop _merge

tab hml32

tab hw1 hml32

[Updated on: Mon, 24 October 2022 07:52] by Moderator

Report message to a moderator

Previous Topic: Merging Male with Birth data?
Next Topic: Merging Datasets PDHS
Goto Forum:
  


Current Time: Mon Feb 6 00:26:17 Coordinated Universal Time 2023