Mergin problem for two data-sets with a common string and numeric "id"

[Akira Nagamine]

Hello.

I want to merge two datasets that contains "Gini" and "Trade" data by country and year.


My master data looks like this

* Example generated by -dataex-. For more info, type help dataex clear
* dataex cntry_id year gini

input str2 cntry_id float year double gini
"AA" 1992      0
"AA" 2002      0
"AA" 2012      0
"AC" 1992      0
"AC" 2002      0
"AC" 2012      0
"AF" 1992 .56387
"AF" 2002 .63446
"AF" 2012  .7609
"AG" 1992  .7966
"AG" 2002  .7914
"AG" 2012 .64001
"AL" 1992   .376
"AL" 2002 .55118
"AL" 2012 .28738
"AN" 1992      0
"AN" 2002      0
"AN" 2012      0
"AO" 1992 .75707
"AO" 2002 .70831
"AO" 2012 .48078
"AQ" 1992      0
"AQ" 2002      0
"AQ" 2012      0
"AR" 1992 .68955
"AR" 2002 .60649
"AR" 2012 .57158
"AS" 1992 .44822
"AS" 2002 .57196
"AS" 2012 .63146
"AU" 1992 .22183
"AU" 2002 .35457
"AU" 2012 .33598
"AV" 1992      0
"AV" 2002      0
"AV" 2012      0
"BA" 1992      0
"BA" 2002      0
"BA" 2012      0
"BC" 1992 .59581
"BC" 2002 .47707
"BC" 2012 .48557
"BD" 1992      0
"BD" 2002      0
"BD" 2012      0
"BE" 1992 .37956
"BE" 2002 .39035
"BE" 2012 .39551
"BF" 1992      0
"BF" 2002      0
"BF" 2012      0
"BG" 1992 .29408
"BG" 2002 .30649
"BG" 2012 .27113
"BH" 1992 .05084
"BH" 2002 .15278
"BH" 2012 .21364
"BL" 1992  .8152
"BL" 2002  .8053
"BL" 2012 .77077
"BM" 1992   .531
"BM" 2002 .66887
"BM" 2012 .65689
"BN" 1992 .23637
"BN" 2002 .22501
"BN" 2012 .28818
"BP" 1992      0
"BP" 2002      0
"BP" 2012      0
"BR" 1992 .74052
"BR" 2002 .78221
"BR" 2012 .64287
"BT" 1992 .27995
"BT" 2002 .36256
"BT" 2012 .31166
"BU" 1992 .32268
"BU" 2002 .25693
"BU" 2012 .23862
"BX" 1992 .57379
"BX" 2002 .62735
"BX" 2012 .42061
"BY" 1992      0
"BY" 2002 .49454
"BY" 2012  .2754
end

My using data looks like this

* Example generated by -dataex-. For more info, type help dataex clear
* dataex cntry_id year trade

input str2 cntry_id float year double trade
"AA" 1992                  0
"AA" 2002  134.3021582733813
"AA" 2012 147.67467489530526
"AC" 1992                  0
"AC" 2002 163.90534966322997
"AC" 2012 151.52815245103184
"AE" 1992                  0
"AE" 2002  93.07165881477808
"AE" 2012 164.24934796072355
"AF" 1992                  0
"AF" 2002                  0
"AF" 2012                  0
"AG" 1992  49.18908423604113
"AG" 2002 61.134171447472596
"AG" 2012  65.40497919812694
"AJ" 1992 140.79998340283396
"AJ" 2002  92.81814432989691
"AJ" 2012  78.26306954042201
"AL" 1992 108.78547224115917
"AL" 2002  68.52506822343872
"AL" 2012  76.51020062109332
"AM" 1992 101.07675940552565
"AM" 2002  73.98450184323094
"AM" 2012  75.96167596067043
"AN" 1992                  0
"AN" 2002                  0
"AN" 2012                  0
"AO" 1992                  0
"AO" 2002  105.3017426001488
"AO" 2012  91.80009860646764
"AQ" 1992                  0
"AQ" 2002           210.9375
"AQ" 2012          176.40625
"AR" 1992 14.730980586353045
"AR" 2002  41.75272435856421
"AR" 2012 30.526542371710804
"AS" 1992 33.049085350005434
"AS" 2002  41.49665355972043
"AS" 2012  43.17744185240205
"AU" 1992   67.6301676560879
"AU" 2002  86.94816799966057
"AU" 2012 105.15217742783814
"BA" 1992 181.12403716972682
"BA" 2002 131.13363421639184
"BA" 2012   185.884650580276
"BB" 1992  75.92233009708738
"BB" 2002   83.4717527764365
"BB" 2012   89.7597885032538
"BC" 1992  89.75125458681512
"BC" 2002   91.8357740585774
"BC" 2012 110.65489500955488
"BD" 1992                  0
"BD" 2002                  0
"BD" 2012  73.78230306162189
"BE" 1992  115.0692875016359
"BE" 2002  136.0851494880806
"BE" 2012 160.74603022932368
"BF" 1992 101.46156320360245
"BF" 2002  70.27798170509257
"BF" 2012   84.2507345739471
"BG" 1992 19.934005502451058
"BG" 2002 28.967380721164922
"BG" 2012 48.110922747678906
"BH" 1992 102.57523868515044
"BH" 2002 119.63576551374761
"BH" 2012  128.2565794614501
"BK" 1992                  0
"BK" 2002   78.7274794065835
"BK" 2012  88.14510901301165
"BL" 1992  49.11085696987636
"BL" 2002 49.350260466537996
"BL" 2012  84.94876143267012
"BM" 1992                  0
"BM" 2002                  0
"BM" 2012 11.855398125982113
"BN" 1992  56.74074580485531
"BN" 2002 43.768422536431096
"BN" 2012  50.73673677911344
"BO" 1992  117.3913043478261
"BO" 2002 131.00205828229073
"BO" 2012 153.08526931337192
"BP" 1992   149.746192893401
"BP" 2002 45.124037639007696
"BP" 2012 105.47945205479452
"BR" 1992 19.253372526255763
"BR" 2002 27.618357530623864
"BR" 2012  25.11427360865339
"BT" 1992   91.7608375674865
"BT" 2002  75.47709852773367
"BT" 2012 104.18684182318312
"BU" 1992 100.04980079681276
"BU" 2002   75.2696110143524
"BU" 2012 123.99356144747887
"BX" 1992 105.78136463683052
"BX" 2002 108.74796908221474
"BX" 2012 105.64102397775221
"BY" 1992 38.226131412797606
"BY" 2002  21.67382940696661
"BY" 2012  43.69998900710379
end

As you can see, the countries are not perfectly matched between the master data and the using data.

So, when I type

 merge 1:1 cntry_id year using maindata_trade.dta

The result shows

variables cntry_id year do not uniquely identify observations in the using data

Thank you very much for your help.

Akira

[Øyvind Snilsberg]

merge 1:m cntry_id year using maindata_trade

then you need to decide what to do with country-years with missing gini or trade

[Akira Nagamine]

Thanks for your advice! I was now able to merge two data.