The DHS Program User Forum
Discussions regarding The DHS Program data and results
Home » Data » Merging data files » How to link child health outcome with mothers' characteristics
How to link child health outcome with mothers' characteristics [message #25315] Sat, 01 October 2022 14:26 Go to next message
Asantie is currently offline  Asantie
Messages: 3
Registered: October 2021
Member
Hello,

I am using the 2019 Ethiopian Mini DHS data to assess factors predicting child health outcomes. So I would like to merge child characteristics based on household members (PR) file to the characteristics of the specific mothers of the children based on women record (IR). I now how to merge simply PR files and IR files based on the identifier variables in IR(v001, v002, v003)and PR(hv001 hv002 hvidx)files. But I do not know how to merge the under 5 child's health outcome with characteristics of his/her mother (like age and mother's bmi etc) in the same row. Remember that one mother may have more than one under 5 child. So how can I link the information of children to their specific mothers correctly using PR and IR files?
Re: How to link child health outcome with mothers' characteristics [message #25320 is a reply to message #25315] Mon, 03 October 2022 09:53 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3189
Registered: February 2013
Senior Member
Following is a response from DHS staff member, Tom Pullum:

You just need to merge the mothers in the KR file with the women in the PR file. This could be a "m:1" merge or you can use an older version of the merge command, which I prefer. The following Stata code shows how to do it, and shows that the mother's PR data is repeated for each child.

cd e:\DHS\scratch

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\ETPR81FL.DTA", clear
keep if hv117==1
gen cluster=hv001
gen hh=hv002
gen mo_line=hvidx
sort cluster hh mo_line
save ETPR81temp.dta, replace

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\ETKR81FL.DTA", clear
gen cluster=v001
gen hh=v002
gen mo_line=v003
sort cluster hh mo_line
merge cluster hh mo_line using ETPR81temp.dta

tab _merge

keep if _merge==3
save ETKRPR81FL.dta, replace

gen children_per_mother=1
collapse(sum) children_per_mother, by(cluster hh mo_line)

tab children
Re: How to link child health outcome with mothers' characteristics [message #28445 is a reply to message #25320] Tue, 09 January 2024 01:51 Go to previous messageGo to next message
nyamukondiwamel@gmail.com is currently offline  nyamukondiwamel@gmail.com
Messages: 5
Registered: January 2024
Member
I am also trying to merge mother's information and children under 5 using stata for Tanzania 2022 DHS but i keep getting the following responses when i try to use the commands for merging data

master data not sorted
or
variables cluster hh mo_line do not uniquely identify observations in the master data

may you please help me.
Re: How to link child health outcome with mothers' characteristics [message #28453 is a reply to message #28445] Tue, 09 January 2024 11:26 Go to previous messageGo to next message
nyamukondiwamel@gmail.com is currently offline  nyamukondiwamel@gmail.com
Messages: 5
Registered: January 2024
Member
I am trying to merge KR data for children under 5 with the household data HR as well as pregnant women/mothers IR data for Malaria research using Tanzania DHS data for 2023, may you kindly help me when I follow commands I get responses such as
File TZPR825L not found
Or
Invalid filelist:quotes do not match
Or
Master data not sorted
Or variables cluster hh mo_line do not uniquely identify observations in the master data
Re: How to link child health outcome with mothers' characteristics [message #28492 is a reply to message #28453] Thu, 18 January 2024 13:43 Go to previous messageGo to next message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3189
Registered: February 2013
Senior Member
Following is a response from Senior DHS staff member, Tom Pullum:

The following Stata lines will add the mother's data and the household-level data to the child's record. Let us know if you need something else.

* Merge the KR, IR, and HR files for the Tanzania 2022 survey

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

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\TZHR82FL.DTA", clear 

* Just keep household-level variables 
drop *_*

gen cluster=hv001
gen hh=hv002
sort cluster hh
save HRtemp.dta, replace

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\TZIR82FL.DTA", clear 
gen cluster=v001
gen hh=v002
gen mo_line=v003
sort cluster hh mo_line
save IRtemp.dta, replace

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\TZKR82FL.DTA", clear 
gen cluster=v001
gen hh=v002
gen mo_line=v003

* Merge the child with the mother's data
* The KR file already includes many of the mother's variables; they will be over-written
sort cluster hh mo_line
quietly merge cluster hh mo_line using IRtemp.dta
tab _merge
keep if _merge==3
drop _merge

* Merge the child and mother with the household data
sort cluster hh
quietly merge cluster hh using HRtemp.dta
tab _merge
keep if _merge==3
drop _merge

Re: How to link child health outcome with mothers' characteristics [message #28506 is a reply to message #28492] Mon, 22 January 2024 07:54 Go to previous messageGo to next message
nyamukondiwamel@gmail.com is currently offline  nyamukondiwamel@gmail.com
Messages: 5
Registered: January 2024
Member
Thank you for the response however i am still failing to merge it , i have attached the response i am getting below.
Re: How to link child health outcome with mothers' characteristics [message #28507 is a reply to message #28506] Mon, 22 January 2024 11:32 Go to previous message
Bridgette-DHS is currently offline  Bridgette-DHS
Messages: 3189
Registered: February 2013
Senior Member

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

You modified the two merge commands by inserting "1:1". You can replace "1:1" with "m:1" in both places or you can just omit "1:1" completely. The reason for replacing the first "1" with "m" would be that a mother can have more than 1 child in the past 5 years and a household can include more than 1 mother ("m" is an abbreviation for "many"). I had intentionally omitted that part of the merge command.

Previous Topic: Merging and appending data files
Next Topic: Merge BDSQ7RDT and BDVA7RDT with the household data file
Goto Forum:
  


Current Time: Thu Nov 7 19:07:15 Coordinated Universal Time 2024