The DHS Program User Forum: Merging data files » Merging the child, household and wealth file for Philippines 1998

Home » Data » Merging data files » Merging the child, household and wealth file for Philippines 1998

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

Merging the child, household and wealth file for Philippines 1998 [message #12829]

Wed, 19 July 2017 16:59

chlansangan
Messages: 6
Registered: July 2017

Member

Hello,

I'm trying to merge the child, household and wealth file into one file for analysis.
What have I done so far is to use country code and phase, cluster number and household number to merge the child and household file together.
Then, I use the household id number to merge the child&household file with the wealth file.

Whenever I do this, I lose the variables I need from the wealth file. I've been working on this for a few months now and still cannot figure out what's wrong.
I've tried merging the files different ways and I still lose variables that I require to do my analysis.

Thanks,
Charmaine

[Updated on: Wed, 19 July 2017 17:20]

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12832 is a reply to message #12829]

Thu, 20 July 2017 07:16

Bridgette-DHS
Messages: 3214
Registered: February 2013

Senior Member

Following is a response from Senior DHS Stata Specialist, Tom Pullum:

I assume that you are using PHPR52FL.dta as the household file and PHKR52FL.dta as the child file. Both of these files already have the wealth index on them. In the household file it is hv270. In the KR (and IR and BR) files it is v190. There is no need to merge with the WI file.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12835 is a reply to message #12832]

Thu, 20 July 2017 12:31

chlansangan
Messages: 6
Registered: July 2017

Member

Hello,

I was actually looking at the 1998 files not 2008. The file names are PHHR3BFL, PHKR3BFL, and PHWI3AFL.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12840 is a reply to message #12835]

Fri, 21 July 2017 12:18

Bridgette-DHS
Messages: 3214
Registered: February 2013

Senior Member

Following is a response from Senior DHS Stata Specialist, Tom Pullum:

Ok -- your original query did say 2008, not 1998.

You can merge the PR and WI files using hhid and whhid, as follows:

use e:\DHS\DHS_data\WI_files\PHWI3AFL.dta, clear
rename whhid hhid
sort hhid
save e:\DHS\DHS_data\scratch\PHtemp.dta, replace

use e:\DHS\DHS_data\PR_files\PHPR3BFL.dta, clear
sort hhid
merge hhid using e:\DHS\DHS_data\scratch\PHtemp.dta

However, the KR file for this survey does not include b16. There have been several references to b16 in other forum posts. Without b16, you cannot merge the KR and PR files. Sorry about that.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12870 is a reply to message #12840]

Tue, 25 July 2017 14:41

chlansangan
Messages: 6
Registered: July 2017

Member

Thank you for your reply.

I just don't understand why I was able to merge the child file with the household (using v000, v001 and v002) the household with wealth (using household id) but not the merged files of child and house with wealth? Or child with the merged house and wealth file? Can you please clarify?

I was able to do this for 1993 and there was no problem merging the child and house, then merging that merged file with the wealth file.

Again, thank you for your response. If it is easier to talk on the phone, please let me know.

Thank you!
Charmaine

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12873 is a reply to message #12870]

Tue, 25 July 2017 17:28

Bridgette-DHS
Messages: 3214
Registered: February 2013

Senior Member

Following is a response from Senior DHS Stata Specialist, Tom Pullum:

When doing a merge you can almost always ignore v000. The id for a person in the PR file is hv001 hv002 hvidx, which will match with a woman in the IR file using v001 v002 v003 or with a child in the KR file using v001 v002 b16, but in this survey you do not have b16. You must have b16 to get a merge with hvidx, and the older files do not have b16.

The WI file has one record per household. The HR file has one record per household. I can't tell whether you are referring to the HR file or the PR file as the "household" file. You CAN merge the WI file with the HR file or the KR file with the HR file, without using the line numbers. Please tell me whether you were using the HR file or the PR file.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12882 is a reply to message #12873]

Thu, 27 July 2017 17:09

chlansangan
Messages: 6
Registered: July 2017

Member

The name of the child file, I'm using is PHKR3BFL.DTA, the name for the household is PHHR3BFL.DTA and for the wealth file, I'm using PHWI3AFL.DTA.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12885 is a reply to message #12882]

Fri, 28 July 2017 07:26

Bridgette-DHS
Messages: 3214
Registered: February 2013

Senior Member

Another response from Senior DHS Stata Specialist, Tom Pullum:

The reason why you were able to merge the KR file with the HR file is that the HR file has one record per household. This is a very wide file--that is, the records are very long and each record contains information about everyone in the household. The people in the household have subscripts for their individual-level information. What you did in that merge was not very useful. The merged record for each child would have attached to it all the information about all the people in the household.

A much more useful merge, and the one I thought you were trying to do, was with the KR and PR files. The PR file is long, rather than wide, with one record for each person in the household. Each record includes information about the household as a whole, such as water and sanitation, but the individual-level information is just about the one individual. For that file you need to match b16 in the KR file with hvidx in the PR file. If you do not have b16, then you are stuck. Fortunately, this was realized about 20 years ago and the KR files since then include b16.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12895 is a reply to message #12885]

Tue, 01 August 2017 13:55

chlansangan
Messages: 6
Registered: July 2017

Member

Thank you for your response.

I just want to clarify again. The research I'm doing only needs household level data for each child. Therefore, I think that it was okay for me to use the HR file since household level data should not change between the people in the household including the child. For example, if one household had 10 members and two were children, the 10 members should have the same household level data. The two children should also have the same information. Therefore, the issue with the PR file not having b16 should not be a problem. Please let me know what you think.

Also, I did not have problems merging the KR file with the HR file. The problem only arises when I merged it (the merged file of KR & HR) with the WI file.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12898 is a reply to message #12895]

Wed, 02 August 2017 12:47

Bridgette-DHS
Messages: 3214
Registered: February 2013

Senior Member

Another response from Senior DHS Stata Specialist, Tom Pullum:

If you only want to attach household level data to the child, then you are fine. You do not need line numbers to do that.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #12941 is a reply to message #12898]

Wed, 16 August 2017 13:41

chlansangan
Messages: 6
Registered: July 2017

Member

Thank you for the clarification.

However, I am still having trouble with merging the KR, HR and WI files. Whenever I merge KR and HR file, it turns out fine.
When I merge those two files together with WI, the variables in the WI files disappears (the actual variable still exist but the observations are missing or labeled in STATA as a dot).
This is also what happens when I merge the HR and WI files together, it turns out fine. When I merge those two files together with KR, variables that I need disappears.

Again, I did not have trouble doing this for 1993, only in 1998. I was able to merge the KR, HR and WI file without a problem.

Report message to a moderator

Re: Merging the child, household and wealth file for Philippines 1998 [message #13550 is a reply to message #12941]

Fri, 17 November 2017 12:21

Bridgette-DHS
Messages: 3214
Registered: February 2013

Senior Member

An email was sent to the user.

Report message to a moderator

Previous Topic:	DRC merge individuals records contained in household data to child data
Next Topic:	Thoughts on pooling surveys from single country

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Wed Dec 25 21:12:16 Coordinated Universal Time 2024