Dear Friends,
I generated a panel data from raw material using SQL, grouped and ordered by countries and years. However, I realized that SQL does not return some of the country-year dyads because some groupings do not have any value for specific country-year. However, these values are not missing, they are "0". I need to find a way to insert the missing country year dyad and assign the value "0" for these rows.
Best,
------------------ copy up to and including the previous line ------------------
Listed 100 out of 1683 observations
Use the count() option to list more
I generated a panel data from raw material using SQL, grouped and ordered by countries and years. However, I realized that SQL does not return some of the country-year dyads because some groupings do not have any value for specific country-year. However, these values are not missing, they are "0". I need to find a way to insert the missing country year dyad and assign the value "0" for these rows.
Best,
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str37 recipient int year float amountgrant "Afghanistan" 2000 1113800.8 "Afghanistan" 2001 2446247.3 "Afghanistan" 2002 15609534 "Afghanistan" 2003 14994850 "Afghanistan" 2004 28665932 "Afghanistan" 2005 48972180 "Afghanistan" 2006 19458870 "Afghanistan" 2007 21895556 "Afghanistan" 2008 28806104 "Afghanistan" 2009 8707406 "Afghanistan" 2010 25351238 "Afghanistan" 2011 17174934 "Afghanistan" 2012 694219 "Afghanistan" 2014 2568095 "Afghanistan" 2015 26163968 "Afghanistan" 2016 45765568 "Afghanistan" 2017 88572696 "Albania" 2000 2673122 "Albania" 2003 1374388 "Albania" 2004 5955135 "Albania" 2005 2324775.3 "Albania" 2007 0 "Albania" 2009 0 "Albania" 2010 1232805.9 "Albania" 2011 1652099.4 "Albania" 2012 80723.14 "Albania" 2013 0 "Albania" 2014 0 "Albania" 2015 1564194.3 "Albania" 2016 1691729 "Albania" 2017 5005773 "Algeria" 2003 1277285.3 "Algeria" 2004 2388520.8 "Algeria" 2007 801245.9 "Algeria" 2009 1811110.3 "Algeria" 2010 57136568 "Algeria" 2012 0 "Algeria" 2015 0 "Algeria" 2016 17897466 "Algeria" 2017 29596978 "America, regional" 2003 0 "America, regional" 2017 5000000 "Angola" 2001 1309537 "Angola" 2002 3913893.5 "Angola" 2005 1884110.5 "Angola" 2006 1016265.5 "Angola" 2007 410568.2 "Angola" 2009 0 "Angola" 2010 54190464 "Angola" 2011 331232.4 "Angola" 2012 16144628 "Angola" 2013 31605510 "Angola" 2014 54745.19 "Angola" 2015 31283.885 "Angola" 2016 12858680 "Angola" 2017 30000 "Antigua and Barbuda" 2006 35921788 "Antigua and Barbuda" 2008 4902111 "Antigua and Barbuda" 2009 4563002.5 "Antigua and Barbuda" 2010 1257873.8 "Antigua and Barbuda" 2011 3631232 "Antigua and Barbuda" 2012 15414359 "Antigua and Barbuda" 2013 6005047 "Antigua and Barbuda" 2014 12765809 "Antigua and Barbuda" 2015 1238127.3 "Antigua and Barbuda" 2016 0 "Antigua and Barbuda" 2017 2175000 "Argentina" 2009 0 "Argentina" 2010 0 "Armenia" 2002 1310535.3 "Armenia" 2003 2043656.5 "Armenia" 2004 1791390.6 "Armenia" 2007 0 "Armenia" 2008 2475703.5 "Armenia" 2009 847414.8 "Armenia" 2010 16962420 "Armenia" 2012 3289611 "Armenia" 2014 13044997 "Armenia" 2015 9446880 "Armenia" 2016 0 "Armenia" 2017 14798489 "Azerbaijan" 2002 1310535.3 "Azerbaijan" 2004 2388520.8 "Azerbaijan" 2005 3448173 "Azerbaijan" 2011 0 "Azerbaijan" 2012 0 "Azerbaijan" 2014 4692445 "Azerbaijan" 2016 14452393 "Azerbaijan" 2017 3341499 "Bahamas" 2004 52745156 "Bahamas" 2005 94177.27 "Bahamas" 2006 5290.3 "Bahamas" 2009 0 "Bahamas" 2011 32040.28 "Bahamas" 2014 549338.2 "Bahamas" 2015 147034.27 "Bahamas" 2016 76075.96 "Bahamas" 2017 50000 "Bangladesh" 2002 28831776 "Bangladesh" 2004 1869660.6 end
Listed 100 out of 1683 observations
Use the count() option to list more
Comment