Merging variables in unbalanced panel data considering two variables

[John Davies]

Hi all,

I am working on an unbalanced panel data in which both countries and years are listed in the columns. As in some countries there are multiple events recurring during the same year, there are as many rows. On another dataset I stored for each country and year a specific variable changing in a ten years time span (reg). My question is how to possibly merge/append the "reg" values into the main dataset considering both country and year as I failed multiple time following the merge commands.
Many thanks for the people out there reading and much more for those trying to help me!

Kind regards,
John

* Example generated by -dataex-. For more info, type help dataex
clear
input int eventid str58 country byte(pop gdppop) str25 region int year byte(reg)
  1 "Afghanistan" . . "Southern Asia"   2007 . . . . .   20104 .
  2 "Afghanistan" . . "Southern Asia"   2007 . . . . .    2973 .
  3 "Afghanistan" . . "Southern Asia"   2007 . . . . .      36 .
  4 "Afghanistan" . . "Southern Asia"   2007 . . . . .    2509 .
  5 "Afghanistan" . . "Southern Asia"   2007 . . . . .     161 .
  6 "Afghanistan" . . "Southern Asia"   2007 . . . . .     655 .
  7 "Afghanistan" . . "Southern Asia"   2007 . . . . .    4113 .
  8 "Afghanistan" . . "Southern Asia"   2008 . . . . .  172001 .
  9 "Afghanistan" . . "Southern Asia"   2008 . . . . .  280000 .
 10 "Afghanistan" . . "Southern Asia"   2008 . . . . .    1180 .
 11 "Afghanistan" . . "Southern Asia"   2009 . . . . .    2519 .
 12 "Afghanistan" . . "Southern Asia"   2009 . . . . .   60055 .
 13 "Afghanistan" . . "Southern Asia"   2009 . . . . .      42 .
 14 "Afghanistan" . . "Southern Asia"   2009 . . . . .      11 .
 15 "Afghanistan" . . "Southern Asia"   2010 . . . . .    5065 .
 16 "Afghanistan" . . "Southern Asia"   2010 . . . . .   40070 .
 17 "Afghanistan" . . "Southern Asia"   2010 . . . . .     299 .
 18 "Afghanistan" . . "Southern Asia"   2010 . . . . .      35 .
 19 "Afghanistan" . . "Southern Asia"   2011 . . . . .    3090 .
 20 "Afghanistan" . . "Southern Asia"   2011 . . . . . 1750000 .
 21 "Afghanistan" . . "Southern Asia"   2011 . . . . .      26 .
 22 "Afghanistan" . . "Southern Asia"   2011 . . . . .    9782 .
 23 "Afghanistan" . . "Southern Asia"   2012 . . . . .     230 .
 24 "Afghanistan" . . "Southern Asia"   2012 . . . . .    4335 .
 25 "Afghanistan" . . "Southern Asia"   2012 . . . . .   13151 .
 26 "Afghanistan" . . "Southern Asia"   2012 . . . . .   27843 .
 27 "Afghanistan" . . "Southern Asia"   2012 . . . . .      20 .
 28 "Afghanistan" . . "Southern Asia"   2012 . . . . .    5720 .
 29 "Afghanistan" . . "Southern Asia"   2012 . . . . .      69 .
 30 "Afghanistan" . . "Southern Asia"   2012 . . . . .      43 .
 31 "Afghanistan" . . "Southern Asia"   2012 . . . . .      45 .
 32 "Afghanistan" . . "Southern Asia"   2012 . . . . .     113 .
 33 "Afghanistan" . . "Southern Asia"   2013 . . . . .    5010 .
 34 "Afghanistan" . . "Southern Asia"   2013 . . . . .    9520 .
 35 "Afghanistan" . . "Southern Asia"   2013 . . . . .    2649 .
 36 "Afghanistan" . . "Southern Asia"   2013 . . . . .      31 .
 37 "Afghanistan" . . "Southern Asia"   2013 . . . . .    1024 .
 38 "Afghanistan" . . "Southern Asia"   2014 . . . . .   10116 .
 39 "Afghanistan" . . "Southern Asia"   2014 . . . . .  140531 .
 40 "Afghanistan" . . "Southern Asia"   2014 . . . . .      63 .
 41 "Afghanistan" . . "Southern Asia"   2015 . . . . .   33311 .
 42 "Afghanistan" . . "Southern Asia"   2015 . . . . .     552 .
 43 "Afghanistan" . . "Southern Asia"   2015 . . . . .    3017 .
 44 "Afghanistan" . . "Southern Asia"   2015 . . . . .      62 .
 45 "Afghanistan" . . "Southern Asia"   2015 . . . . .     106 .
 46 "Afghanistan" . . "Southern Asia"   2016 . . . . .      30 .
 47 "Afghanistan" . . "Southern Asia"   2016 . . . . .       6 .
 48 "Afghanistan" . . "Southern Asia"   2016 . . . . .      31 .
 49 "Afghanistan" . . "Southern Asia"   2016 . . . . .      17 .
 50 "Afghanistan" . . "Southern Asia"   2017 . . . . .      31 .
 51 "Afghanistan" . . "Southern Asia"   2017 . . . . .    9091 .
 52 "Afghanistan" . . "Southern Asia"   2017 . . . . .     413 .
 53 "Afghanistan" . . "Southern Asia"   2017 . . . . .      73 .
 54 "Afghanistan" . . "Southern Asia"   2017 . . . . .    1858 .
 55 "Albania"     . . "Southern Europe" 2007 . . . . .     150 .
 56 "Albania"     . . "Southern Europe" 2007 . . . . .      75 .
 57 "Albania"     . . "Southern Europe" 2009 . . . . .    6600 .
 58 "Albania"     . . "Southern Europe" 2010 . . . . .   14000 .
 59 "Albania"     . . "Southern Europe" 2012 . . . . .  230005 .
 60 "Albania"     . . "Southern Europe" 2015 . . . . .   42000 .
 61 "Albania"     . . "Southern Europe" 2015 . . . . .       0 .
 62 "Albania"     . . "Southern Europe" 2015 . . . . .     301 .
 63 "Albania"     . . "Southern Europe" 2016 . . . . .    4502 .
 64 "Albania"     . . "Southern Europe" 2017 . . . . .   21003 .
 65 "Albania"     . . "Southern Europe" 2017 . . . . .       6 .
 66 "Algeria"     . . "Northern Africa" 2007 . . . . .      18 .
 67 "Algeria"     . . "Northern Africa" 2007 . . . . .      15 .
 68 "Algeria"     . . "Northern Africa" 2007 . . . . .      12 .
 69 "Algeria"     . . "Northern Africa" 2007 . . . . .      22 .
 70 "Algeria"     . . "Northern Africa" 2007 . . . . .     103 .
 71 "Algeria"     . . "Northern Africa" 2007 . . . . .    1217 .
 72 "Algeria"     . . "Northern Africa" 2007 . . . . .       8 .
 73 "Algeria"     . . "Northern Africa" 2008 . . . . .   59143 .
 74 "Algeria"     . . "Northern Africa" 2009 . . . . .    2581 .
 75 "Algeria"     . . "Northern Africa" 2009 . . . . .      15 .
 76 "Algeria"     . . "Northern Africa" 2011 . . . . .     803 .
 77 "Algeria"     . . "Northern Africa" 2012 . . . . .      94 .
 78 "Algeria"     . . "Northern Africa" 2012 . . . . .      76 .
 79 "Algeria"     . . "Northern Africa" 2015 . . . . .      12 .
 80 "Algeria"     . . "Northern Africa" 2015 . . . . .   35000 .
 81 "Algeria"     . . "Northern Africa" 2017 . . . . .  125000 .
 82 "Angola"      . . "Middle Africa"   2007 . . . . .    6000 .
 83 "Angola"      . . "Middle Africa"   2007 . . . . .   40105 .
 84 "Angola"      . . "Middle Africa"   2008 . . . . .   81407 .
 85 "Angola"      . . "Middle Africa"   2009 . . . . .     417 .
 86 "Angola"      . . "Middle Africa"   2009 . . . . .  220060 .
 87 "Angola"      . . "Middle Africa"   2009 . . . . .    4650 .
 88 "Angola"      . . "Middle Africa"   2010 . . . . .  110893 .
 89 "Angola"      . . "Middle Africa"   2010 . . . . .   75000 .
 90 "Angola"      . . "Middle Africa"   2010 . . . . .    3913 .
 91 "Angola"      . . "Middle Africa"   2011 . . . . .      17 .
 92 "Angola"      . . "Middle Africa"   2011 . . . . .   65197 .
 93 "Angola"      . . "Middle Africa"   2011 . . . . .    5500 .
 94 "Angola"      . . "Middle Africa"   2011 . . . . .   20100 .
 95 "Angola"      . . "Middle Africa"   2012 . . . . . 1833900 .
 96 "Angola"      . . "Middle Africa"   2013 . . . . .    1009 .
 97 "Angola"      . . "Middle Africa"   2015 . . . . .    2001 .
 98 "Angola"      . . "Middle Africa"   2015 . . . . .    2005 .
 99 "Angola"      . . "Middle Africa"   2015 . . . . .    1069 .
100 "Angola"      . . "Middle Africa"   2016 . . . . .      54 .
end

[William Lisowski]

Without having seen the merge commands you tried, and what Stata told you in response, and without having seen your second dataset, it's difficult to give definitive advice.

I'd expect the merging to be something like

use panel
merge m:1 country year using reference

Please do the following:

use panel, clear obs(20)
dataex country year
use reference, clear obs(20)
dataex country year reg

[John Davies]

Dear William,

Thank you for the swift reply.
I had forgotten to add the year variable in the merge.

In my next post I will be more professional.

Thank you again,

Kind regards,
John