| Merging 2018 Zambia HR to IR [message #26011] |
Thu, 26 January 2023 22:46  |
azimkl10
Messages: 3
Registered: January 2023
|
Member |
|
|
Hi everyone,
I need help with merging 2018 Zambia HR to IR and I am running into many issues.
First, my objective is to get each child between ages 5-18 as a row/observation for data analysis and variables of interest are in the household and individual dataset. I have tried merging household data to individual data by joining v001 and v002 and hv001 and hv002 but that did not work. One issue I have is that each mother is the row/observation and each child is represented in the columns meaning that when asked about the mother's children, each child is a different column of the same question. For example, HV122: Education level attended during the current school year (Primary, Secondary, etc.) is my outcome variable and it goes from hv122_01 to hv122_28. This means that there is a mother(s) who has had up to 28 children and each of the children is represented in a column instead of a row. I tried to pivot_longer the columns of interest and merging but I am doing something wrong. I have my code in R below but I have Stata as well so I am open to Stata solutions.
TL:DR - How can I merge each household to an individual and make children the row/observation INSTEAD of the columns in the datasets.
Thank you.
|
|
|
|
| Re: Merging 2018 Zambia HR to IR [message #26015 is a reply to message #26011] |
Fri, 27 January 2023 09:49  |
Bridgette-DHS
Messages: 3036
Registered: February 2013
|
Senior Member |
|
|
Following is a response from Senior DHS staff member, Tom Pullum:
The PR file and/or KR file would be better for your purposes. In the PR file, each member of the household, including children, has their own record. Height, weight, and Z scores for children under 5 are in the hc variables. The KR file has a separate record for each child born in the past 5 years for the women who were interviewed. Height, weight, and Z scores are in the hw variables, with the same numbering as for the hc variables in the PR file.
The PR file can actually be constructed from the HR file with a "reshape long" command in Stata. Similarly, the KR file can be constructed from the IR file with "reshape long". But you don't have to construct them--they are among the standard recode files.
Let us know if you have any difficulties.
|
|
|
|
|
|
| Re: Merging 2018 Zambia HR to IR [message #26060 is a reply to message #26058] |
Thu, 02 February 2023 16:16 |
Bridgette-DHS
Messages: 3036
Registered: February 2013
|
Senior Member |
|
|
Following is a response from Senior DHS staff member, Tom Pullum:
The following Stata lines will merge the PR and IR files, with one record for each woman in the IR file. Hope this is what you are looking for.
* PR/IR merge for ZM71, Zambia 2018 DHS
* specify a workspace
cd e:\DHS\DHS_data\scratch
use "...ZMIR71FL.DTA", clear
gen cluster=v001
gen hh=v002
gen line=v003
sort cluster hh line
save ZMIRtemp.dta, replace
use "...ZMPR71FL.DTA", clear
keep if hv117==1
gen cluster=hv001
gen hh=hv002
gen line=hvidx
sort cluster hh line
merge cluster hh line using ZMIRtemp.dta
tab _merge
keep if _merge==3
drop _merge
* save this file with another name
|
|
|
|
The DHS Program User Forum
Members
Search
Help
Register
Login
Home
