Merging data

[Sherine Maui]

Hello,

I am trying to merge variables from the original Demographic and Health surveys (DHS) with the IPUMS-DHS data. The unique identifier in both datasets is "caseid". My master dataset (IPUMS) has a caseid and year variable (2005 and 2014) plus all other measures. In the original DHS they also have caseid, year variable (2005 ONLY) plus another variable which I would like to merge in.



I run the following command using my master dataset:

merge m:1 caseid year using "original DHS data file"

Which returns the following:

    Result                           # of obs.
    -----------------------------------------
    not matched                       126,364
        from master                   106,890  (_merge==1)
        from using                     19,474  (_merge==2)

    matched                                 0  (_merge==3)
    -----------------------------------------

Nothing has been matched. I have tried m:1 and 1:1 and I get the same result. Can anyone advise?

[Dung Le]

Hi Maul,

I think the fastest way to get an answer for your problem is to provide an example of your data using dataex, please refer to Statalist FAQ. Without looking at your data, people may not say much about your problem.

Best regards,

DL

[Sherine Maui]

Thank you Dung Le.

Please see below an example of my data (Master file = IPUMS DHS)

* Example generated by -dataex-. To install: ssc install dataex
clear
input long sample int(country year) str18 caseid long v001 int v002
81801 818 1988 "       10051001  2" 1005 1001
81801 818 1988 "       10051002  2" 1005 1002
81801 818 1988 "       10051004  2" 1005 1004
81801 818 1988 "       10051007  2" 1005 1007
81801 818 1988 "       10051008  2" 1005 1008
81801 818 1988 "       10051010  2" 1005 1010
81801 818 1988 "       10051012  2" 1005 1012
81801 818 1988 "       10051014  1" 1005 1014
81801 818 1988 "       10051015  2" 1005 1015
81801 818 1988 "       10051016  2" 1005 1016
81801 818 1988 "       10051018  2" 1005 1018
81801 818 1988 "       10141001  2" 1014 1001
81801 818 1988 "       10141003  2" 1014 1003
81801 818 1988 "       10141004  2" 1014 1004
81801 818 1988 "       10141005  2" 1014 1005
81801 818 1988 "       10141006  2" 1014 1006
81801 818 1988 "       10141007  7" 1014 1007
81801 818 1988 "       10141007 11" 1014 1007
81801 818 1988 "       10141008  2" 1014 1008
81801 818 1988 "       10141009  2" 1014 1009
81801 818 1988 "       10141010  2" 1014 1010
81801 818 1988 "       10141011  2" 1014 1011
81801 818 1988 "       10141012  2" 1014 1012
81801 818 1988 "       10141013  2" 1014 1013
81801 818 1988 "       10141014  3" 1014 1014
81801 818 1988 "       10141015  2" 1014 1015
81801 818 1988 "       10141016  2" 1014 1016
81801 818 1988 "       10141017  2" 1014 1017
81801 818 1988 "       10141019  2" 1014 1019
81801 818 1988 "       10141020  2" 1014 1020
81801 818 1988 "       10141021  2" 1014 1021
81801 818 1988 "       10141022  2" 1014 1022
81801 818 1988 "       20011001  2" 2001 1001
81801 818 1988 "       20011002  1" 2001 1002
81801 818 1988 "       20011003  2" 2001 1003
81801 818 1988 "       20011004  2" 2001 1004
81801 818 1988 "       20011007  2" 2001 1007
81801 818 1988 "       20011007  8" 2001 1007
81801 818 1988 "       20011007  9" 2001 1007
81801 818 1988 "       20011008  1" 2001 1008
81801 818 1988 "       20011009  2" 2001 1009
81801 818 1988 "       20011010  2" 2001 1010
81801 818 1988 "       20011011  2" 2001 1011
81801 818 1988 "       20011012  2" 2001 1012
81801 818 1988 "       20011014  3" 2001 1014
81801 818 1988 "       20011015  2" 2001 1015
81801 818 1988 "       20011016  2" 2001 1016
81801 818 1988 "       20011017  2" 2001 1017
81801 818 1988 "       20011019  2" 2001 1019
81801 818 1988 "       20011020  2" 2001 1020
81801 818 1988 "       20011022  4" 2001 1022
81801 818 1988 "       20011024  2" 2001 1024
81801 818 1988 "       20011027  2" 2001 1027
81801 818 1988 "       20011028  2" 2001 1028
81801 818 1988 "       20011028  8" 2001 1028
81801 818 1988 "       20011029  2" 2001 1029
81801 818 1988 "       20011030  2" 2001 1030
81801 818 1988 "       20011031  2" 2001 1031
81801 818 1988 "       20011033  2" 2001 1033
81801 818 1988 "       20011034  2" 2001 1034
81801 818 1988 "       20121003  2" 2012 1003
81801 818 1988 "       20121004  2" 2012 1004
81801 818 1988 "       20121006  2" 2012 1006
81801 818 1988 "       20121009  2" 2012 1009
81801 818 1988 "       30121001  2" 3012 1001
81801 818 1988 "       30121002  2" 3012 1002
81801 818 1988 "       30121003  2" 3012 1003
81801 818 1988 "       30121004  2" 3012 1004
81801 818 1988 "       30121005  1" 3012 1005
81801 818 1988 "       30121007  2" 3012 1007
81801 818 1988 "       30121008  2" 3012 1008
81801 818 1988 "       30121009  2" 3012 1009
81801 818 1988 "       30121011  2" 3012 1011
81801 818 1988 "       30121012  2" 3012 1012
81801 818 1988 "       30121013  2" 3012 1013
81801 818 1988 "       30121014  2" 3012 1014
81801 818 1988 "       30121015  2" 3012 1015
81801 818 1988 "       30121016  2" 3012 1016
81801 818 1988 "       30121017  2" 3012 1017
81801 818 1988 "       30121018  2" 3012 1018
81801 818 1988 "       30121021  2" 3012 1021
81801 818 1988 "       30121023  2" 3012 1023
81801 818 1988 "       30121024  2" 3012 1024
81801 818 1988 "       30121006  2" 3012 1006
81801 818 1988 "       30121006  3" 3012 1006
81801 818 1988 "       30561001  2" 3056 1001
81801 818 1988 "       30561004  2" 3056 1004
81801 818 1988 "       30561005  2" 3056 1005
81801 818 1988 "       30561007  2" 3056 1007
81801 818 1988 "       30561008  2" 3056 1008
81801 818 1988 "       30561009  1" 3056 1009
81801 818 1988 "       30561009 10" 3056 1009
81801 818 1988 "       30561010  2" 3056 1010
81801 818 1988 "       30561011  2" 3056 1011
81801 818 1988 "       30561013  2" 3056 1013
81801 818 1988 "       30561014  2" 3056 1014
81801 818 1988 "       30561017  2" 3056 1017
81801 818 1988 "       30561018  2" 3056 1018
81801 818 1988 "       30561020  2" 3056 1020
81801 818 1988 "       30561023  2" 3056 1023
end
label values sample SAMPLE
label def SAMPLE 81801 "egypt 1988", modify
label values country COUNTRY
label def COUNTRY 818 "egypt", modify
label values year YEAR
label def YEAR 1988 "1988", modify

And then the merging file (original DHS):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str15 caseid long v001 int v002 byte s706 float year
"    11306  6  2" 11306   6 0 2005
"    11306 16  2" 11306  16 0 2005
"    11306 25  2" 11306  25 0 2005
"    11306 34  2" 11306  34 0 2005
"    11306 34  7" 11306  34 0 2005
"    11306 53  1" 11306  53 0 2005
"    11306 82  2" 11306  82 0 2005
"    11306 91  2" 11306  91 0 2005
"    11306100  2" 11306 100 6 2005
"    11306119  2" 11306 119 0 2005
"    11306129  2" 11306 129 6 2005
"    11306138  2" 11306 138 0 2005
"    12604  3  2" 12604   3 0 2005
"    12604 13  2" 12604  13 0 2005
"    12604 45  2" 12604  45 0 2005
"    12604 65  2" 12604  65 1 2005
"    12604 86  4" 12604  86 0 2005
"    12604107  2" 12604 107 0 2005
"    12604117  2" 12604 117 0 2005
"    12604128  2" 12604 128 1 2005
"    12604128  8" 12604 128 0 2005
"    12604138  2" 12604 138 0 2005
"    12604138  4" 12604 138 0 2005
"    12604159  2" 12604 159 4 2005
"    12604159  6" 12604 159 0 2005
"    12604179  1" 12604 179 0 2005
"    20506 17  2" 20506  17 3 2005
"    20506 28  2" 20506  28 0 2005
"    20506 38  2" 20506  38 0 2005
"    20506 43  3" 20506  43 0 2005
"    20506 48  2" 20506  48 0 2005
"    20506 58  2" 20506  58 4 2005
"    20506 63  3" 20506  63 3 2005
"    20506 63  4" 20506  63 0 2005
"    20506 74  2" 20506  74 0 2005
"    20506 79  2" 20506  79 0 2005
"    20506 84  2" 20506  84 0 2005
"    20506 94  2" 20506  94 0 2005
"    20506 99  2" 20506  99 3 2005
"    20506104  1" 20506 104 0 2005
"    20506109  2" 20506 109 0 2005
"    20506114  2" 20506 114 0 2005
"    20506125  3" 20506 125 0 2005
"    20506130  2" 20506 130 0 2005
"    20506135  2" 20506 135 0 2005
"    20506145  2" 20506 145 0 2005
"    20506150  2" 20506 150 4 2005
"    20506160  3" 20506 160 0 2005
"    20506165  2" 20506 165 7 2005
"    20506170  2" 20506 170 0 2005
"    20506181  2" 20506 181 0 2005
"    21202  5  4" 21202   5 0 2005
"    21202 14  3" 21202  14 0 2005
"    21202 23  2" 21202  23 4 2005
"    21202 32  2" 21202  32 6 2005
"    21202 40  2" 21202  40 6 2005
"    21202 49  7" 21202  49 0 2005
"    21202 58  2" 21202  58 1 2005
"    21202 58  6" 21202  58 1 2005
"    21202 76  2" 21202  76 3 2005
"    21202 85  2" 21202  85 0 2005
"    21202102  3" 21202 102 0 2005
"    21202111  3" 21202 111 0 2005
"    21202120  2" 21202 120 0 2005
"    21202129  3" 21202 129 0 2005
"    21202146  2" 21202 146 0 2005
"    21202155  2" 21202 155 3 2005
"    21202155  5" 21202 155 1 2005
"    21202164  2" 21202 164 0 2005
"    21202173  2" 21202 173 0 2005
"    30204 23  2" 30204  23 0 2005
"    30204 33  2" 30204  33 1 2005
"    30204 43  2" 30204  43 0 2005
"    30204 63  2" 30204  63 6 2005
"    30204 73  2" 30204  73 0 2005
"    30204 84  2" 30204  84 0 2005
"    30204104  2" 30204 104 0 2005
"    30204114  2" 30204 114 0 2005
"    30204124  2" 30204 124 3 2005
"    30608 13  2" 30608  13 0 2005
"    30608 19  2" 30608  19 2 2005
"    30608 43  2" 30608  43 0 2005
"    30608 50  2" 30608  50 5 2005
"    30608 56  2" 30608  56 0 2005
"    30608 62  2" 30608  62 0 2005
"    30608 86  2" 30608  86 2 2005
"    30608 92  2" 30608  92 4 2005
"    30608105  2" 30608 105 0 2005
"    40604 12  2" 40604  12 0 2005
"    40604 18  2" 40604  18 5 2005
"    40604 18  4" 40604  18 1 2005
"    40604 24  2" 40604  24 0 2005
"    40604 30  2" 40604  30 0 2005
"    40604 36  2" 40604  36 4 2005
"    40604 54  2" 40604  54 0 2005
"    40604 72  2" 40604  72 2 2005
"    40604 84  2" 40604  84 1 2005
"    40604 90  2" 40604  90 5 2005
"    40604 96  2" 40604  96 0 2005
"    40604102  2" 40604 102 0 2005
end
label values s706 S706
label def S706 0 "no relation", modify
label def S706 1 "first cousin father's side", modify
label def S706 2 "first cousin mother's side", modify
label def S706 3 "second cousin father's side", modify
label def S706 4 "second cousin mother's side", modify
label def S706 5 "other relative father's side", modify
label def S706 6 "other relative mother's side", modify
label def S706 7 "relative by marriage", modify

The variable I would like to merge is labelled "s706"

[Dung Le]

Hi Maui,

I am not sure about the structure of your data but it seems that you encounter a similar problem as this thread https://www.statalist.org/forums/for...t-observations. Please read throughout the thread, especially #6. Hope this helps.

Best regards,

DL