The DHS Program User Forum: Merging data files » How to link child health outcome with mothers' characteristics

Home » Data » Merging data files » How to link child health outcome with mothers' characteristics

Show: Today's Messages :: Show Polls :: Message Navigator

How to link child health outcome with mothers' characteristics [message #25315]

Sat, 01 October 2022 14:26

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?

Report message to a moderator

Re: How to link child health outcome with mothers' characteristics [message #25320 is a reply to message #25315]

Mon, 03 October 2022 09:53

Bridgette-DHS
Messages: 3230
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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: How to link child health outcome with mothers' characteristics [message #28445 is a reply to message #25320]

Tue, 09 January 2024 01:51

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.

Report message to a moderator

Re: How to link child health outcome with mothers' characteristics [message #28453 is a reply to message #28445]

Tue, 09 January 2024 11:26

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

Report message to a moderator

Re: How to link child health outcome with mothers' characteristics [message #28492 is a reply to message #28453]

Thu, 18 January 2024 13:43

Bridgette-DHS
Messages: 3230
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.

Code: [Select all] [Show/ hide]

* 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

Report message to a moderator

Re: How to link child health outcome with mothers' characteristics [message #28506 is a reply to message #28492]

Mon, 22 January 2024 07:54

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.

Attachment: Tanzania merge error.jpg
(Size: 144.12KB, Downloaded 135 times)

Report message to a moderator

Re: How to link child health outcome with mothers' characteristics [message #28507 is a reply to message #28506]

Mon, 22 January 2024 11:32

Bridgette-DHS
Messages: 3230
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Thu May 22 15:19:12 Coordinated Universal Time 2025