The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Merging data files » Struggling with merging Senegal KR data file to mother's file (Struggling with merging Senegal KR data to mother's info PR + IR)
Struggling with merging Senegal KR data file to mother's file [message #18425] Thu, 28 November 2019 03:02 Go to next message
petyr is currently offline  petyr
Messages: 3
Registered: November 2019
Member
Hello,

I have been struggling mightily trying to merge the PR, KR and IR data sets for the 2012-2014 Senegal dataset. I have looked into the different ideas proposed in the forum as well as watched the YouTube videos but I continue to be unsuccessful. In the end, I want to be sure that I have successfully linked all of the kids to their mothers. I am using the numbers and % of children in the 2014 report with fever 2 weeks prior as a check against my work.
The report indicates a rate of 11.4% among 5942 (6,842 unweighted observations in KR). Meanwhile, I am finding 6,223 unweighted observations and ending up with a rate of 11.9% among 6,239.

I am not sure why I am missing kids info during the merge.

Below is the code that I have come up so far. Any help with troubleshooting to replicate DHS results would greatly appreciated.

Thank you

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

set more off
clear
****Prepare KR for merge
use "SNKR6RFL.DTA", clear
count
gen hvidx=b16
gen in_KR=1
sort v001 v002 hvidx
save "KRtemp14.dta", replace

****Prepare IR for merge
use "SNIR6RFL.DTA", clear
rename v003 hvidx
gen mo_line=hvidx
sort v001 v002 mo_line
drop h22*
save "IRtemp14.dta", replace

****Prepare PR for merge
use "SNPR6RFL.DTA", clear
gen v001=hv001
gen v002=hv002
gen in_PR=1
sort v001 v002 hvidx

* Merge PR with KR to identify the children who are living with their mother

quietly merge m:m v001 v002 hvidx using "KRtemp14.dta"
rename _merge _merge_PR_KR
tab1 in_*,m
drop if in_PR==.
gen in_PR_BR=1
gen mo_line=hv112

*Use the IR file to attach information about the child's mother
sort v001 v002 mo_line
quietly merge m:m v001 v002 mo_line using "IRtemp14.dta"
rename _merge _merge_PR_BR_IR
tab1 in_*,m
keep if in_PR==1

keep hhid hvidx hv000 hv001 hv002 hv003 hv004 hv005 hv006 hv007 hv008 hv021 hv022 ///
hv023 hv024 hv042 hv103 hml1 hc1 h22 hc55 hc56 hml1a hml2 b5 h32* h46b h47 ///
shzone shmzone shseason shml1a sh221aa sh221ab sh221ac sh221ad sh221ae sh221af sh221ag ///
sh221ah sh221ay sh225a sh231a hml16 hml16a hml30 hml32 hml33 hml35 ml13* v005 v007 v021 v024

save "IR_KR_PR_merge2012_2014.dta", replace
Re: Struggling with merging Senegal KR data file to mother's file [message #18499 is a reply to message #18425] Tue, 10 December 2019 01:39 Go to previous messageGo to next message
petyr is currently offline  petyr
Messages: 3
Registered: November 2019
Member
Hi all,

Still struggling with this. Any guidance would be greatly appreciated!
Re: Struggling with merging Senegal KR data file to mother's file [message #18513 is a reply to message #18425] Wed, 11 December 2019 10:12 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3017
Registered: February 2013
Senior Member


Following is a response from DHS Research & Data Analysis Director, Tom Pullum:

If you try to merge the KR file with the PR file, there are four reasons why you may appear to lose cases:
(1) Children in the KR file will not be in the PR file if they have died (b16=.)
(2) Children in the KR file will not be in the PR file if they are not living with the mother (b16=0)
(3) Children in the PR file will not be in the KR file if their mother has died (hv112=.)
(4) Children in the PR file will not be in the KR file if they are not living with their mother (hv112=0)

Have you taken these possibilities into account? They will be identified after a merge by _merge= 1or 2 rather than 3. If this does not answer your question, please let us know.
Re: Struggling with merging Senegal KR data file to mother's file [message #18600 is a reply to message #18513] Thu, 09 January 2020 23:22 Go to previous messageGo to next message
petyr is currently offline  petyr
Messages: 3
Registered: November 2019
Member
Thank you for your response. I previously had not considered the different possibilities you pointed out in your response. But even when I do, I still continue to struggle with replicating report results with my dataset merges. I had some further questions, regarding the merge. In the measurements of fever or those of parasitemia among children under 5, are children who are not living with their mothers considered in the calculations? How about those whose mothers have died? Or are all eligible children in the household tested and fever info collected?

Please assist!
Re: Struggling with merging Senegal KR data file to mother's file [message #18607 is a reply to message #18600] Fri, 10 January 2020 09:30 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3017
Registered: February 2013
Senior Member

Following is a response from DHS Research & Data Analysis Director, Tom Pullum:

When measurements of children are included in the household part of a DHS survey, ALL children on the household roster are measured, regardless of whether their mother is still living or is in the household. Those measurements will appear in the PR file. The measurements will re-appear in the KR file with the exceptions I mentioned earlier.

There is one other reason why you may not get a match with the report and I should have mentioned it earlier. Tables in the report (and statistics in STATcompiler) derived from the PR file are usually limited to de facto cases (hv103=1). Please try again with a restriction to the children in the PR file who have hv103=1. (There are a couple of tables, e.g. the one on survival and coresidence of parents, that are limited to de jure cases (hv102=1) because they are in parallel with MICS tables, which are always de jure rather than de facto.) The de facto restriction is now my best guess at why you are not getting a match and I do apologize for not mentioning it earlier.
Re: Struggling with merging Senegal KR data file to mother's file [message #24243 is a reply to message #18513] Sat, 26 March 2022 20:22 Go to previous messageGo to next message
id709nvz is currently offline  id709nvz
Messages: 43
Registered: March 2019
Member
Dear Expert,
While merging PR to KR file (for example, average years of education of the household, number of elderly in hh, number of youth in hh, etc),
Is it possible to merge with KR without lose of cases? if so how in Stata? I've successfully merged but I lost a few cases (equivalent to the number of dead children). And when using death as an outcome and those variables as an independent variable, no coefficient estimate emerges.

Kind regards,
Re: Struggling with merging Senegal KR data file to mother's file [message #24305 is a reply to message #24243] Fri, 15 April 2022 16:02 Go to previous message
admin is currently offline  admin
Messages: 50
Registered: November 2012
Senior Member
Administrator
Following is response from DHS Research & Data Analysis Director, Tom Pullum:

The PR file includes all household members. The IR file includes women who were eligible for the women's interview, in terms of age and de facto residency. Some women who are eligible for the IR file do not actually get into it because (for one reason or another) the interview does not happen. The KR file includes children born in the past 5 years to women in the IR file. It includes children who died and some children who live elsewhere, so they are not in the PR file.

For these reasons, you cannot get an exact match between the PR file and the KR file, either for the children or for the mothers. You definitely will not find "elderly" in the KR file, because the mothers must be in the age range 15-49. Mothers in the KR file are repeated for each child.

If this does not answer your question, please be more specific about which data files you are using and which cases or variables you want to match.


We now have repositories of code written in Stata and SPSS available on Github. Please reference these code repositories as a resource for code for matching or calculating DHS indicators. The code repositories can be found at:

https://github.com/DHSProgram/DHS-Indicators-Stata
https://github.com/DHSProgram/DHS-Indicators-SPSS

Previous Topic: Mismatch of file names within the same survey
Next Topic: Merge Other Biomarkers with HH
Goto Forum:
  


Current Time: Fri Mar 29 04:47:16 Coordinated Universal Time 2024