I am using country-year panel data using all countries from 1998-2017 with 3772 observations. My main variable of interest is US Foreign Aid to each country. I have another dataset which lists all the contiguous neighbors to each country. Link to dataset: https://github.com/geodatasource/cou...RY-BORDERS.CSV.
I am trying to sum the total amount of US Foreign Aid that was directed to a country and all the countries that border it.
One way I thought to potentially do this (but don't know how to write the code): create groups that are defined as all countries that border a country including the country itself. For example, group1 would be the countries that border Afghanistan, including Afghanistan. I could then sum the US Foreign Aid from each country into a "Group" variable.
Below are Dataex code from the Foreign Aid dataset and from the Country-Borders dataset.
Any help with how to do this is appreciated.
Foreign Aid Dataex
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input float fiscalyear str42 country double(mil_obsHistUSD mil_obsConsUSD Econ_obsHistUSD Econ_obsConsUSD) byte _mergeFA 1998 "Afghanistan" 0 0 8526376 12226440 2 1999 "Afghanistan" 0 0 35382262 50100857 2 2000 "Afghanistan" 0 0 54072046 75005301 2 2001 "Afghanistan" 0 0 91934517 124544710 2 2002 "Afghanistan" 51447000 68585885 457021569 609272236 3 2003 "Afghanistan" 343320500 449110427 640496663 837857713 3 2004 "Afghanistan" 537567587 686213860 1402450722 1790251397 3 2005 "Afghanistan" 707119000 875167213 1022806025 1265877876 3 2006 "Afghanistan" 1883714000 2257939321 1120347331 1342919516 3 2007 "Afghanistan" 3882291000 4530542192 1097835263 1281147907 3 2008 "Afghanistan" 6215767000 7106159117 2642508569 3021040900 3 2009 "Afghanistan" 6032931000 6817815323 2931530132 3312922203 3 2010 "Afghanistan" 6796875225 7614144222 4076315499 4566459303 3 2011 "Afghanistan" 10263512572 11268624849 3136853931 3444048020 3 2012 "Afghanistan" 9560934222 10308951211 3551043835 3828866174 3 2013 "Afghanistan" 7082152450 7509420608 2654311026 2814446325 3 2014 "Afghanistan" 4881953506 5083943659 2378115126 2476509311 3 2015 "Afghanistan" 6141944956 6319777833 1989394112 2046994707 3 2016 "Afghanistan" 3890135762 3957013883 1169948584 1190062016 3 2017 "Afghanistan" 4420602210 4420602210 1309874210 1309874210 3 1998 "Albania" 2313000 3316738 44692948 64087680 3 1999 "Albania" 5379257 7616963 46912775 66427925 3 2000 "Albania" 2371994 3290279 69968502 97055853 3 2001 "Albania" 9431000 12776281 46285817 62703910 3 2002 "Albania" 4866000 6487043 37949677 50592107 3 2003 "Albania" 8857000 11586174 42065583 55027567 3 2004 "Albania" 20171975 25749857 37913930 48397755 3 2005 "Albania" 11268347 13946291 32266363 39934527 3 2006 "Albania" 13533739 16222399 40038012 47992105 3 2007 "Albania" 8858772 10337978 25710680 30003758 3 2008 "Albania" 2758000 3153076 28082134 32104827 3 2009 "Albania" 4145000 4684265 49564670 56013034 3 2010 "Albania" 5080000 5690829 31909240 35746065 3 2011 "Albania" 5424418 5955634 22032894 24190589 3 2012 "Albania" 5193263 5599567 31992559 34495553 3 2013 "Albania" 4042453 4286335 22658462 24025454 3 2014 "Albania" 3990321 4155420 22793716 23736804 3 2015 "Albania" 3520204 3622127 24504843 25214354 3 2016 "Albania" 10379130 10557566 17080232 17373872 3 2017 "Albania" 8058904 8058904 13974954 13974954 3 1998 "Algeria" 126000 180678 1343200 1926089 3 1999 "Algeria" 124000 175583 366500 518959 3 2000 "Algeria" 115000 159521 1084673 1504589 3 2001 "Algeria" 121000 163920 2857210 3870694 3 2002 "Algeria" 67000 89320 3963616 5284041 3 2003 "Algeria" 612000 800580 3102506 4058506 3 2004 "Algeria" 719000 917815 3422486 4368860 3 2005 "Algeria" 913000 1129976 1591582 1969823 3 2006 "Algeria" 823000 986500 2855747 3423078 3 2007 "Algeria" 1681000 1961687 4398613 5133077 3 2008 "Algeria" 695000 794557 12004305 13723890 3 2009 "Algeria" 884000 999008 11178098 12632368 3 2010 "Algeria" 1015000 1137046 14566134 16317592 3 2011 "Algeria" 1389932 1526049 9892045 10860779 3 2012 "Algeria" 1719402 1853922 10799742 11644679 3 2013 "Algeria" 1909912 2025138 15151131 16065202 3 2014 "Algeria" 1772160 1845483 10961131 11414647 3 2015 "Algeria" 1747128 1797714 12968743 13344237 3 2016 "Algeria" 1428382 1452938 16213024 16491753 3 2017 "Algeria" 1315415 1315415 8838157 8838157 3 1998 "Angola" 0 0 73787225 105807567 2 1999 "Angola" 0 0 54042484 76523507 2 2000 "Angola" 0 0 105389883 146190137 2 2001 "Angola" 0 0 77212845 104601099 2 2002 "Angola" 0 0 124460350 165922661 2 2003 "Angola" 302000 395058 164810163 215594356 3 2004 "Angola" 500000 638258 119112461 152049015 3 2005 "Angola" 313000 387385 67155495 83115130 3 2006 "Angola" 641000 768344 80281578 96230600 3 2007 "Angola" 439000 512302 53056363 61915525 3 2008 "Angola" 347000 396707 57181110 65372153 3 2009 "Angola" 474000 535667 53937927 60955254 3 2010 "Angola" 373000 417850 99501057 111465250 3 2011 "Angola" 418000 458935 81476806 89455881 3 2012 "Angola" 434600 468602 73733677 79502367 3 2013 "Angola" 488000 517441 54502405 57790551 3 2014 "Angola" 493500 513918 54591798 56850526 3 2015 "Angola" 717500 738274 57608258 59276237 3 2016 "Angola" 577000 586920 66572546 67717041 3 2017 "Angola" 494140 494140 75100836 75100836 3 1998 "Antigua and Barbuda" 4676000 6705174 0 0 1 1999 "Antigua and Barbuda" 11254000 15935529 0 0 1 2000 "Antigua and Barbuda" 706000 979318 0 0 1 2001 "Antigua and Barbuda" 756000 1024161 0 0 1 2002 "Antigua and Barbuda" 3623000 4829955 0 0 1 2003 "Antigua and Barbuda" 332000 434301 0 0 1 2004 "Antigua and Barbuda" 1532000 1955623 0 0 1 2005 "Antigua and Barbuda" 652000 806949 5972 7391 3 2006 "Antigua and Barbuda" 433000 519022 16756 20085 3 2007 "Antigua and Barbuda" 540000 630167 0 0 1 2008 "Antigua and Barbuda" 106000 121184 0 0 1 2009 "Antigua and Barbuda" 680000 768468 25086 28350 3 2010 "Antigua and Barbuda" 2194000 2457811 118288 132511 3 2011 "Antigua and Barbuda" 379000 416116 27233 29900 3 2012 "Antigua and Barbuda" 426000 459329 50000 53912 3 2013 "Antigua and Barbuda" 525500 557203 0 0 1 2014 "Antigua and Barbuda" 512000 533184 0 0 1 2015 "Antigua and Barbuda" 385000 396148 0 0 1 2016 "Antigua and Barbuda" 632000 642865 3781 3846 3 2017 "Antigua and Barbuda" 774110 774110 244302 244302 3 end label values _mergeFA _merge label def _merge 1 "master only (1)", modify label def _merge 2 "using only (2)", modify label def _merge 3 "matched (3)", modify
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str2 country_code str52 country_name str2 country_border_code str52 country_border_name "AD" "Andorra" "FR" "France" "AD" "Andorra" "ES" "Spain" "AE" "United Arab Emirates" "OM" "Oman" "AE" "United Arab Emirates" "SA" "Saudi Arabia" "AF" "Afghanistan" "CN" "China" "AF" "Afghanistan" "IR" "Iran (Islamic Republic of)" "AF" "Afghanistan" "PK" "Pakistan" "AF" "Afghanistan" "TJ" "Tajikistan" "AF" "Afghanistan" "TM" "Turkmenistan" "AF" "Afghanistan" "UZ" "Uzbekistan" "AG" "Antigua and Barbuda" "" "" "AI" "Anguilla" "" "" "AL" "Albania" "GR" "Greece" "AL" "Albania" "ME" "Montenegro" "AL" "Albania" "MK" "North Macedonia" "AL" "Albania" "RS" "Serbia" "AM" "Armenia" "AZ" "Azerbaijan" "AM" "Armenia" "GE" "Georgia" "AM" "Armenia" "IR" "Iran (Islamic Republic of)" "AM" "Armenia" "TR" "Turkey" "AO" "Angola" "CG" "Congo" "AO" "Angola" "CD" "Congo (the Democratic Republic of the)" "AO" "Angola" "NA" "Namibia" "AO" "Angola" "ZM" "Zambia" "AQ" "Antarctica" "" "" "AR" "Argentina" "BO" "Bolivia (Plurinational State Of)" "AR" "Argentina" "BR" "Brazil" "AR" "Argentina" "CL" "Chile" "AR" "Argentina" "PY" "Paraguay" "AR" "Argentina" "UY" "Uruguay" "AS" "American Samoa" "" "" "AT" "Austria" "CZ" "Czechia" "AT" "Austria" "DE" "Germany" "AT" "Austria" "HU" "Hungary" "AT" "Austria" "IT" "Italy" "AT" "Austria" "LI" "Liechtenstein" "AT" "Austria" "SK" "Slovakia" "AT" "Austria" "SI" "Slovenia" "AT" "Austria" "CH" "Switzerland" "AU" "Australia" "" "" "AW" "Aruba" "" "" "AX" "Aland Islands" "" "" "AZ" "Azerbaijan" "AM" "Armenia" "AZ" "Azerbaijan" "GE" "Georgia" "AZ" "Azerbaijan" "IR" "Iran (Islamic Republic of)" "AZ" "Azerbaijan" "RU" "Russian Federation" "AZ" "Azerbaijan" "TR" "Turkey" "BA" "Bosnia and Herzegovina" "HR" "Croatia" "BA" "Bosnia and Herzegovina" "ME" "Montenegro" "BA" "Bosnia and Herzegovina" "RS" "Serbia" "BB" "Barbados" "" "" "BD" "Bangladesh" "IN" "India" "BD" "Bangladesh" "MM" "Myanmar" "BE" "Belgium" "FR" "France" "BE" "Belgium" "DE" "Germany" "BE" "Belgium" "LU" "Luxembourg" "BE" "Belgium" "NL" "Netherlands" "BF" "Burkina Faso" "BJ" "Benin" "BF" "Burkina Faso" "CI" "Cote d’Ivoire" "BF" "Burkina Faso" "GH" "Ghana" "BF" "Burkina Faso" "ML" "Mali" "BF" "Burkina Faso" "NE" "Niger" "BF" "Burkina Faso" "TG" "Togo" "BG" "Bulgaria" "GR" "Greece" "BG" "Bulgaria" "MK" "North Macedonia" "BG" "Bulgaria" "RO" "Romania" "BG" "Bulgaria" "RS" "Serbia" "BG" "Bulgaria" "TR" "Turkey" "BH" "Bahrain" "" "" "BI" "Burundi" "CD" "Congo (the Democratic Republic of the)" "BI" "Burundi" "RW" "Rwanda" "BI" "Burundi" "TZ" "Tanzania (the United Republic of)" "BJ" "Benin" "BF" "Burkina Faso" "BJ" "Benin" "NE" "Niger" "BJ" "Benin" "NG" "Nigeria" "BJ" "Benin" "TG" "Togo" "BL" "Saint Barthelemy" "" "" "BM" "Bermuda" "" "" "BN" "Brunei Darussalam" "MY" "Malaysia" "BO" "Bolivia (Plurinational State Of)" "AR" "Argentina" "BO" "Bolivia (Plurinational State Of)" "BR" "Brazil" "BO" "Bolivia (Plurinational State Of)" "CL" "Chile" "BO" "Bolivia (Plurinational State Of)" "PY" "Paraguay" "BO" "Bolivia (Plurinational State Of)" "PE" "Peru" "BQ" "Bonaire, Sint Eustatius and Saba" "" "" "BR" "Brazil" "AR" "Argentina" "BR" "Brazil" "BO" "Bolivia (Plurinational State Of)" "BR" "Brazil" "CO" "Colombia" "BR" "Brazil" "GF" "French Guiana" "BR" "Brazil" "GY" "Guyana" "BR" "Brazil" "PY" "Paraguay" "BR" "Brazil" "PE" "Peru" "BR" "Brazil" "SR" "Suriname" "BR" "Brazil" "UY" "Uruguay" "BR" "Brazil" "VE" "Venezuela (Bolivarian Republic of)" "BS" "Bahamas" "" "" "BT" "Bhutan" "CN" "China" "BT" "Bhutan" "IN" "India" "BV" "Bouvet Island" "" "" "BW" "Botswana" "NA" "Namibia" end
0 Response to Panel Data - Create variable that sums neighbors into group variable
Post a Comment