Having trouble merging panel data

[Hamid muili]

Dear all, I'm trying to merge 2 different data. One of the data was tsfilled while the other is not. I tried to merge them based on country code but they were merged wrongly as the country code is matched with a wrong one.
Here are the data
Data A
----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long(country code) str3 region str28 regionname str3 adminregion str52 adminregionname int year float(gdp_pcap_grwt he_exp_pcap sanitattion inft_mort)
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   1996 -1.8300238         .         .         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   1997  3.8547444         .         .         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   1998 -1.0270895         .         .         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   1999 -1.6895367         .         .         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2000   4.924527         .  97.97311         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2001  2.3554776         .   98.0125         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2002  -2.121342         .  98.05191         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2003  .10704588         .  98.09132         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2004   6.331366         .  98.13072         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2005  -1.377379         .  98.17013         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2006 -.06044307         .  98.20953         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2007    1.83164         .  98.24893         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2008   .5793837         .  98.28834         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2009  -12.76027         .  98.32774         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2010  -3.827863         .  98.36715         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2011   2.402779         .  98.40656         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2012 -1.8393584         .  98.44596         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2013    5.63697         .  98.48536         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2014 -2.2648685         .  98.52477         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2015 -1.2555883         .  98.56417         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2016  1.1211821         .  98.60358         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2017   6.474908         .  98.64298         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2018  1.8764014         .  98.68238         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2019  -2.743402         .  98.72179         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2020  -24.08457         .  98.76119         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2021  27.696865         .   98.8006         .
13 1 "LCN" "Latin America and Caribbean" ""    ""                                   2022  10.553786         .     98.84         .
 2 2 ""    ""                            ""    ""                                   1996  2.6966016         .         .  94.98193
 2 2 ""    ""                            ""    ""                                   1997   1.816198         .         .  93.28858
 2 2 ""    ""                            ""    ""                                   1998  -.6457965         .         .  91.02152
 2 2 ""    ""                            ""    ""                                   1999  .09154794         .         .   88.5114
 2 2 ""    ""                            ""    ""                                   2000   .7457278 117.63688 22.408363  85.83173
 2 2 ""    ""                            ""    ""                                   2001  1.0201706 124.00504  22.76007  82.80681
 2 2 ""    ""                            ""    ""                                   2002   1.288648  122.2928  23.12713  79.70722
 2 2 ""    ""                            ""    ""                                   2003   .4759161  133.9434 23.496975  76.54768
 2 2 ""    ""                            ""    ""                                   2004   2.806433 143.10315  23.86445  73.41652
 2 2 ""    ""                            ""    ""                                   2005  3.3753424  150.6369  24.36331  70.44797
 2 2 ""    ""                            ""    ""                                   2006   3.771373 167.49974  24.86014 67.744194
 2 2 ""    ""                            ""    ""                                   2007    3.74854 180.21806  25.33604  65.06836
 2 2 ""    ""                            ""    ""                                   2008  1.5212027  187.4898  25.86629  62.49701
 2 2 ""    ""                            ""    ""                                   2009 -2.0391796 196.88994  26.40733  60.19387
 2 2 ""    ""                            ""    ""                                   2010  2.3349123  199.2898  26.94782  58.07454
 2 2 ""    ""                            ""    ""                                   2011   .9379843  209.1868  27.09018  56.03621
 2 2 ""    ""                            ""    ""                                   2012 -1.8091524 201.48418 27.621405  54.12332
 2 2 ""    ""                            ""    ""                                   2013   1.397546   213.504 28.160925  52.42163
 2 2 ""    ""                            ""    ""                                   2014   1.199306 209.74716 28.721573   50.8583
 2 2 ""    ""                            ""    ""                                   2015   .1580471  217.7568  29.30585  49.41616
 2 2 ""    ""                            ""    ""                                   2016 -.49562255  221.1829  29.84379  48.04776
 2 2 ""    ""                            ""    ""                                   2017 -.08156405 214.38167 30.382473  46.63863
 2 2 ""    ""                            ""    ""                                   2018 -.19185886  212.0298 30.730436  45.26828
 2 2 ""    ""                            ""    ""                                   2019  -.6334699  214.0765  31.15841  44.08135
 2 2 ""    ""                            ""    ""                                   2020  -5.334358 207.94843  31.54845  43.02778
 2 2 ""    ""                            ""    ""                                   2021  1.6499473         .  31.50157  42.00421
 2 2 ""    ""                            ""    ""                                   2022   .8692691         .  31.74633         .
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 1996          .         .         .       101
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 1997          .         .         .      98.3
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 1998          .         .         .      95.6
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 1999          .         .         .      93.1
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2000          .         .  20.97092      90.6
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2001          .         .  20.98235        88
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2002          .  87.46503  22.54134      85.4
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2003   .9270291   86.2293  24.10033      82.8
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2004 -2.4972546   93.9263  25.66624      80.1
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2005   7.321874 105.83527  27.23919      77.4
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2006  1.0849882 118.18082 28.812265      74.7
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2007  11.803383  112.1307 30.466534        72
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2008  1.8647544  137.5722 32.128704      69.3
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2009  17.143534 153.58186 33.798958      66.8
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2010   11.09922  148.7448  35.47747      64.2
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2011  -3.211364 147.33917 37.164574      61.8
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2012   8.247145 155.38303  38.86031      59.4
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2013  2.0025222 178.07903  40.56485      57.2
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2014  -.9648033 202.26155   42.2784        55
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2015 -1.6664164 215.72523  44.00131        53
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2016  -.3458025 239.18875   45.7336      51.1
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2017 -.25359377 264.54398  47.47762      49.4
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2018 -1.6885766 299.82254  49.23415      47.8
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2019   .9328334  321.5254  51.00348      46.3
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2020  -5.364666  322.5256    52.649      44.8
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2021  -22.96694         .  54.29859      43.4
 1 3 "SAS" "South Asia"                  "SAS" "South Asia (excluding high income)" 2022          .         .  55.95194         .
 3 4 ""    ""                            ""    ""                                   1996  1.9011704         .         .   107.741
 3 4 ""    ""                            ""    ""                                   1997  1.5280327         .         . 105.93042
 3 4 ""    ""                            ""    ""                                   1998   .7396671         .         . 103.88714
 3 4 ""    ""                            ""    ""                                   1999 -1.2521796         .         . 101.56902
 3 4 ""    ""                            ""    ""                                   2000   .9555746  77.22395 21.800837  99.14005
 3 4 ""    ""                            ""    ""                                   2001  2.3383977  80.48478   22.0409  96.53687
 3 4 ""    ""                            ""    ""                                   2002   6.912487  78.17829  22.64892   93.9391
 3 4 ""    ""                            ""    ""                                   2003   2.644609 116.39487  23.28345  91.27731
 3 4 ""    ""                            ""    ""                                   2004   5.025751 118.04094  23.94444  88.62624
 3 4 ""    ""                            ""    ""                                   2005   2.901298  122.2369 24.629375  85.97243
 3 4 ""    ""                            ""    ""                                   2006   2.471765 123.35756  25.33479  83.56201
 3 4 ""    ""                            ""    ""                                   2007   2.621751 123.62226  26.05482  81.25134
 3 4 ""    ""                            ""    ""                                   2008   3.351724 127.14462  26.78844  79.16779
 3 4 ""    ""                            ""    ""                                   2009   3.344952 135.97614 27.537943  77.26925
 3 4 ""    ""                            ""    ""                                   2010  4.0086393 127.60748  28.30494 75.465034
 3 4 ""    ""                            ""    ""                                   2011    1.94459 134.50224 29.087677 73.970924
 3 4 ""    ""                            ""    ""                                   2012   2.260046 135.91458  29.88612  72.48216
 3 4 ""    ""                            ""    ""                                   2013  3.2552686 145.10953 30.700077  71.15285
 3 4 ""    ""                            ""    ""                                   2014   3.090523 152.24864 31.528614  69.98855
end
label values country country1
label def country1 1 "Afghanistan", modify
label def country1 2 "Africa Eastern and Southern", modify
label def country1 3 "Africa Western and Central", modify
label def country1 13 "Aruba", modify
label values code code1
label def code1 1 "ABW", modify
label def code1 2 "AFE", modify
label def code1 3 "AFG", modify
label def code1 4 "AFW", modify

------------------ copy up to and including the previous line ------------------


DATA B
----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 country_code str24 country_name str3 region_code str27 region_name int year str11 welfare_type float(gini headcount) long(code country)
"AGO" "Angola"               "SSA" "Sub-Saharan Africa"          2000 "consumption" .5196 .2141 1   3
""    ""                     ""    ""                            2001 ""                .     . 1   .
""    ""                     ""    ""                            2002 ""                .     . 1   .
""    ""                     ""    ""                            2003 ""                .     . 1   .
""    ""                     ""    ""                            2004 ""                .     . 1   .
""    ""                     ""    ""                            2005 ""                .     . 1   .
""    ""                     ""    ""                            2006 ""                .     . 1   .
""    ""                     ""    ""                            2007 ""                .     . 1   .
"AGO" "Angola"               "SSA" "Sub-Saharan Africa"          2008 "consumption" .4272 .1463 1   3
""    ""                     ""    ""                            2009 ""                .     . 1   .
""    ""                     ""    ""                            2010 ""                .     . 1   .
""    ""                     ""    ""                            2011 ""                .     . 1   .
""    ""                     ""    ""                            2012 ""                .     . 1   .
""    ""                     ""    ""                            2013 ""                .     . 1   .
""    ""                     ""    ""                            2014 ""                .     . 1   .
""    ""                     ""    ""                            2015 ""                .     . 1   .
""    ""                     ""    ""                            2016 ""                .     . 1   .
""    ""                     ""    ""                            2017 ""                .     . 1   .
"AGO" "Angola"               "SSA" "Sub-Saharan Africa"          2018 "consumption" .5127 .3112 1   3
"ALB" "Albania"              "ECA" "Europe & Central Asia"       1996 "consumption" .2701 .0053 2   1
""    ""                     ""    ""                            1997 ""                .     . 2   .
""    ""                     ""    ""                            1998 ""                .     . 2   .
""    ""                     ""    ""                            1999 ""                .     . 2   .
""    ""                     ""    ""                            2000 ""                .     . 2   .
""    ""                     ""    ""                            2001 ""                .     . 2   .
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2002 "consumption" .3174 .0109 2   1
""    ""                     ""    ""                            2003 ""                .     . 2   .
""    ""                     ""    ""                            2004 ""                .     . 2   .
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2005 "consumption"  .306 .0059 2   1
""    ""                     ""    ""                            2006 ""                .     . 2   .
""    ""                     ""    ""                            2007 ""                .     . 2   .
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2008 "consumption" .2998  .002 2   1
""    ""                     ""    ""                            2009 ""                .     . 2   .
""    ""                     ""    ""                            2010 ""                .     . 2   .
""    ""                     ""    ""                            2011 ""                .     . 2   .
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2012 "consumption" .2896 .0062 2   1
""    ""                     ""    ""                            2013 ""                .     . 2   .
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2014 "consumption"  .346 .0102 2   1
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2015 "consumption" .3275 .0012 2   1
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2016 "consumption" .3374 .0014 2   1
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2017 "consumption" .3306 .0039 2   1
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2018 "consumption" .3015 .0005 2   1
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2019 "consumption" .3012     0 2   1
"ALB" "Albania"              "ECA" "Europe & Central Asia"       2020 "consumption" .2942 .0002 2   1
"ARE" "United Arab Emirates" "OHI" "Other High Income Countries" 2013 "income"      .3251     0 3 157
""    ""                     ""    ""                            2014 ""                .     . 3   .
""    ""                     ""    ""                            2015 ""                .     . 3   .
""    ""                     ""    ""                            2016 ""                .     . 3   .
""    ""                     ""    ""                            2017 ""                .     . 3   .
"ARE" "United Arab Emirates" "OHI" "Other High Income Countries" 2018 "income"      .2597     0 3 157
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   1996 "income"      .4952 .0422 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   1997 "income"      .4911 .0377 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   1998 "income"      .5073 .0397 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   1999 "income"      .4979 .0419 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2000 "income"      .5106 .0499 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2001 "income"      .5334 .0823 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2002 "income"      .5379 .1245 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2003 "income"       .509 .0528 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2004 "income"      .4838 .0381 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2005 "income"      .4771 .0263 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2006 "income"      .4635 .0212 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2007 "income"      .4617 .0172 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2008 "income"      .4488 .0159 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2009 "income"      .4365 .0152 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2010 "income"      .4358 .0075 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2011 "income"      .4265 .0071 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2012 "income"      .4133 .0064 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2013 "income"      .4095 .0062 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2014 "income"      .4163 .0061 4   4
""    ""                     ""    ""                            2015 ""                .     . 4   .
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2016 "income"      .4203 .0067 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2017 "income"      .4113 .0042 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2018 "income"      .4134 .0077 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2019 "income"      .4291 .0078 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2020 "income"      .4234 .0107 4   4
"ARG" "Argentina"            "LAC" "Latin America & Caribbean"   2021 "income"      .4201 .0096 4   4
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       1999 "consumption" .3622  .119 5   5
""    ""                     ""    ""                            2000 ""                .     . 5   .
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2001 "consumption" .3536 .1397 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2002 "consumption" .3478 .0997 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2003 "consumption" .3303 .0754 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2004 "consumption" .3751 .0532 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2005 "consumption" .3599 .0263 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2006 "consumption" .2971   .02 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2007 "consumption" .3123 .0154 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2008 "consumption" .2919 .0088 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2009 "consumption" .2802 .0125 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2010 "consumption" .2999 .0095 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2011 "consumption" .2935 .0119 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2012 "consumption"  .296 .0083 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2013 "consumption" .3058 .0174 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2014 "consumption" .3148 .0144 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2015 "consumption" .3236 .0115 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2016 "consumption" .3255 .0111 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2017 "consumption" .3362 .0079 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2018 "consumption" .3443 .0134 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2019 "consumption" .3003 .0103 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2020 "consumption" .2513 .0035 5   5
"ARM" "Armenia"              "ECA" "Europe & Central Asia"       2021 "consumption" .2794 .0052 5   5
"AUS" "Australia"            "OHI" "Other High Income Countries" 2001 "income"      .3346 .0075 6   6
end
label values code code
label def code 1 "AGO", modify
label def code 2 "ALB", modify
label def code 3 "ARE", modify
label def code 4 "ARG", modify
label def code 5 "ARM", modify
label def code 6 "AUS", modify
label values country country
label def country 1 "Albania", modify
label def country 3 "Angola", modify
label def country 4 "Argentina", modify
label def country 5 "Armenia", modify
label def country 6 "Australia", modify
label def country 157 "United Arab Emirates", modify

------------------ copy up to and including the previous line ------------------

[Clyde Schechter]

The variables country and code in both data sets are value-labeled integer variables. But the correspondence between integer value and the labeled code or country name is different in the two data sets. This is not an unusual problem. Whenever one -encode-s "the same variable" in two different data sets, there is the risk that the encodings will work differently and the corresponding integer values in the two data sets will not correspond to the same original string values, unless the two data sets start out with exactly the same set of distinct values in the string variables. That was apparently not the case here.

And, unfortunately, in the example data you show, the string values of the original code and country name are missing in most of the observations. Somehow, the value-labeled integer version of code got filled in.

The only reasonable way to fix this is to undo the damage that was done. Then it is straightforward to merge the two repaired data sets on code and year:

use datasetA, clear
foreach v of varlist code country {
    decode `v', gen(_`v')
    order _`v', after(`v')
    drop `v'
    rename _`v' `v'
}
tempfile holding
save `holding'

use datasetB, clear
foreach v of varlist code country {
    decode `v', gen(_`v')
    order _`v', after(`v')
    drop `v'
    rename _`v' `v'
}
by code (year), sort: replace country = country[_n-1] if missing(country)
drop country_code country_name

merge 1:1 code year using `holding'

Now, the idea of replacing the country names, and possibly the three-letter codes, with value-labeled integers is a good one. If you are going to do panel data analysis, you will need an integer panel variable. The mistake was in doing it separately in the two data sets. But it is fine to go ahead and -encode- these variables now, provided you are not going to combine them with any other data sets. If you will need to combine them with other data sets, then it would be best to combine them first, and only use -encode- once all of the combining is done. (There are some circumstances where this is not practical, and there is another approach, but I don't want to take the time and space to show it here since you probably won't need it.)