Reshaping from long to wide when years are not unique

[Muhammad Saleh]

Hi All,
I have chinese sectoral projects spread across deveoping world data in the following form :



copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input float id str37 Recipient int CommitmentYear str33 oecd_sector double(AmountConstantUSD2021 AmountNominalUSD YAV3 YAVN)
1 "Afghanistan" 2001 "Commodity & Humanitarian Aid" 55319830.88309263 22000000 0 0
1 "Afghanistan" 2002 "Commodity & Humanitarian Aid" 54988007.84408033 22000000 0 0
5 "Afghanistan" 2002 "Social Infra & Services" 0 0 0 0
2 "Afghanistan" 2003 "Eco Infra & Services" 0 0 0 0
5 "Afghanistan" 2003 "Social Infra & Services" 321111867.45105976 131815872.51947829 14595994.109375 14595994.109375
5 "Afghanistan" 2004 "Social Infra & Services" 626354333.5188246 2.750e+08 28470651.4375 28470651.4375
5 "Afghanistan" 2005 "Social Infra & Services" 1241373475.9792924 5.720e+08 56426068.4375 56426068.4375
1 "Afghanistan" 2006 "Commodity & Humanitarian Aid" 448577767.0558164 220762306.6496011 0 0
5 "Afghanistan" 2006 "Social Infra & Services" 44702879.87564741 22000000 2031949.125 2031949.125
3 "Afghanistan" 2008 "General Sector/Other Multi Sector" 96519024.42309932 63308353.45751331 4387228.5 4387228.5
5 "Afghanistan" 2008 "Social Infra & Services" 654593789.5449178 429358411.43546325 28793775 28793775
1 "Afghanistan" 2009 "Commodity & Humanitarian Aid" 145111004.07211086 96611207.25442892 0 0
5 "Afghanistan" 2009 "Social Infra & Services" 0 0 0 0
1 "Afghanistan" 2010 "Commodity & Humanitarian Aid" 7659998.146879317 5500000 348181.7109375 348181.7109375
4 "Afghanistan" 2010 "Prod Sector" 104108496.57494688 74751549.56734341 4732204.5 4732204.5
5 "Afghanistan" 2010 "Social Infra & Services" 40669473.28116706 29201326 1848612.375 1848612.375
5 "Afghanistan" 2011 "Social Infra & Services" 326886993.2833362 265787674.4764209 14858500.25 14858500.25
5 "Afghanistan" 2012 "Social Infra & Services" 0 0 0 0
5 "Afghanistan" 2013 "Social Infra & Services" 0 0 0 0
5 "Afghanistan" 2014 "Social Infra & Services" 64901361.06131359 58623105.962387286 2950061.765625 2950061.765625
5 "Afghanistan" 2015 "Social Infra & Services" 329838474.05032104 293901500.6573369 3221608.4765625 3242432.64453125
1 "Afghanistan" 2016 "Commodity & Humanitarian Aid" 11727962.512953056 9932179.653732076 533089.23046875 533089.23046875
5 "Afghanistan" 2016 "Social Infra & Services" 952701602.8928349 806824157.7238419 25027273.869140625 35181353.869140625
1 "Afghanistan" 2017 "Commodity & Humanitarian Aid" 0 0 0 0
5 "Afghanistan" 2017 "Social Infra & Services" 1956067473.4694314 1697476961.5598428 3306780.03125 3647837.3125
1 "Afghanistan" 2018 "Commodity & Humanitarian Aid" 181261170.98820195 166317578.4928293 8239143.75 8239143.75
2 "Afghanistan" 2018 "Eco Infra & Services" 0 0 0 0
5 "Afghanistan" 2018 "Social Infra & Services" 0 0 0 0
5 "Afghanistan" 2019 "Social Infra & Services" 0 0 0 0
3 "Afghanistan" 2021 "General Sector/Other Multi Sector" 0 0 0 0
5 "Afghanistan" 2021 "Social Infra & Services" 0 0 0 0
5 "Africa, regional" 2004 "Social Infra & Services" 6053980.755598295 2657991.840555349 275180.9296875 275180.9296875
2 "Africa, regional" 2006 "Eco Infra & Services" 223514399.37823704 1.100e+08 0 0
5 "Africa, regional" 2006 "Social Infra & Services" 4485777670.558163 2207623066.4960127 203898980 203898980
2 "Africa, regional" 2012 "Eco Infra & Services" 1712538457.9856782 1458567496.0912757 50250752.5 50250752.5
5 "Africa, regional" 2014 "Social Infra & Services" 0 0 0 0
5 "Africa, regional" 2015 "Social Infra & Services" 49380125.06147379 44000000 2244551.0625 2244551.0625
5 "Africa, regional" 2016 "Social Infra & Services" 2597769.9183886107 2200000 118080.45703125 118080.45703125
5 "Africa, regional" 2019 "Social Infra & Services" 2879712271.1308165 2563014391.6093426 26179203 69811207
2 "Albania" 2000 "Eco Infra & Services" 67754457.64019528 26400000 0 0
2 "Albania" 2003 "Eco Infra & Services" 34835711.48741287 14300000 1583441.4375 1583441.4375
4 "Albania" 2004 "Prod Sector" 60130016.01780714 26400000 0 0
5 "Albania" 2005 "Social Infra & Services" 658882.8449428545 303600 29949.22021484375 29949.22021484375
4 "Albania" 2007 "Prod Sector" 0 0 0 0
3 "Albania" 2009 "General Sector/Other Multi Sector" 0 0 0 0
1 "Albania" 2010 "Commodity & Humanitarian Aid" 612799.8517503456 440000 27854.5400390625 27854.5400390625
5 "Albania" 2010 "Social Infra & Services" 30639992.58751727 22000000 1392726.84375 1392726.84375
3 "Albania" 2011 "General Sector/Other Multi Sector" 0 0 0 0
5 "Albania" 2012 "Social Infra & Services" 2047252.9232185509 1743643.51128014 93056.953125 93056.953125
4 "Albania" 2015 "Prod Sector" 39281867.10959282 35001980.06121654 1785539.4375 1785539.4375
5 "Albania" 2016 "Social Infra & Services" 39093208.376510195 33107265.51244023 1776963.9375 1776963.9375
4 "Albania" 2017 "Prod Sector" 41433441.342868686 35955974.45977365 1883338.13671875 1883338.13671875
5 "Albania" 2017 "Social Infra & Services" 89233888.93712032 77437242.17881313 3191832.33984375 4056085.93359375
4 "Albania" 2018 "Prod Sector" 0 0 0 0
1 "Albania" 2019 "Commodity & Humanitarian Aid" 0 0 0 0
5 "Albania" 2021 "Social Infra & Services" 0 0 0 0
5 "Algeria" 2000 "Social Infra & Services" 0 0 0 0
4 "Algeria" 2002 "Prod Sector" 0 0 0 0
5 "Algeria" 2002 "Social Infra & Services" 0 0 0 0
1 "Algeria" 2003 "Commodity & Humanitarian Aid" 32374510.73293933 13289681.298695732 1471568.625 1471568.625
5 "Algeria" 2005 "Social Infra & Services" 1432354010.7453365 6.600e+08 65106999.5 65106999.5
2 "Algeria" 2007 "Eco Infra & Services" 19791908.211725075 11000000 899632.21875 899632.21875
5 "Algeria" 2007 "Social Infra & Services" 0 0 0 0
5 "Algeria" 2008 "Social Infra & Services" 0 0 0 0
4 "Algeria" 2009 "Prod Sector" 0 0 0 0
5 "Algeria" 2010 "Social Infra & Services" 1448466039.3036106 1040021559.197821 65839368.5 65839368.5
2 "Algeria" 2012 "Eco Infra & Services" 0 0 0 0
5 "Algeria" 2013 "Social Infra & Services" 0 0 0 0
5 "Algeria" 2014 "Social Infra & Services" 0 0 0 0
3 "Algeria" 2015 "General Sector/Other Multi Sector" 0 0 0 0
5 "Algeria" 2015 "Social Infra & Services" 0 0 0 0
3 "Algeria" 2016 "General Sector/Other Multi Sector" 0 0 0 0
4 "Algeria" 2016 "Prod Sector" 62666413.02754579 53070946.61644162 0 0
5 "Algeria" 2016 "Social Infra & Services" 390932083.76510215 331072655.12440234 0 0
5 "Algeria" 2017 "Social Infra & Services" 750326020.5114627 651133537.4428698 0 0
1 "Algeria" 2018 "Commodity & Humanitarian Aid" 2056484.2033828497 1886942.8628906552 93476.5546875 93476.5546875
5 "Algeria" 2018 "Social Infra & Services" 1439538.9423679952 1320860.004023459 65433.591796875 65433.591796875
5 "Algeria" 2019 "Social Infra & Services" 0 0 0 0
1 "Angola" 2001 "Commodity & Humanitarian Aid" 33191898.529855568 13200000 1508722.59375 1508722.59375
5 "Angola" 2001 "Social Infra & Services" 331918985.2985555 1.320e+08 15087226.5 15087226.5
5 "Angola" 2002 "Social Infra & Services" 0 0 0 0
5 "Angola" 2003 "Social Infra & Services" 589527425.1716058 2.420e+08 0 0
4 "Angola" 2005 "Prod Sector" 47759457.23161867 22006600 2170884.5625 2170884.5625
5 "Angola" 2006 "Social Infra & Services" 0 0 0 0
5 "Angola" 2007 "Social Infra & Services" 0 0 0 0
5 "Angola" 2009 "Social Infra & Services" 0 0 0 0
5 "Angola" 2011 "Social Infra & Services" 0 0 0 0
5 "Angola" 2012 "Social Infra & Services" 409450584.6437093 348728702.2560263 18611389.75 18611389.75
5 "Angola" 2013 "Social Infra & Services" 807222928.257506 715599974.744134 36691950.5 36691950.5
5 "Angola" 2014 "Social Infra & Services" 1383530.4071667083 1249694.1255961587 62887.74609375 62887.74609375
5 "Angola" 2015 "Social Infra & Services" 785637.3421918578 700039.6012243307 35710.7900390625 35710.7900390625
1 "Angola" 2016 "Commodity & Humanitarian Aid" 12988849.591943128 11000000 0 0
4 "Angola" 2016 "Prod Sector" 259776991.8388613 2.200e+08 10824041.25 11808045
5 "Angola" 2016 "Social Infra & Services" 53128194.61906772 44993217.9653732 2414917.921875 2414917.921875
5 "Angola" 2017 "Social Infra & Services" 760543.1836338426 660000 34570.1455078125 34570.1455078125
4 "Angola" 2018 "Prod Sector" 0 0 0 0
5 "Angola" 2018 "Social Infra & Services" 18479438.800154675 16955950.887839127 839974.5454101563 839974.5454101563
1 "Angola" 2019 "Commodity & Humanitarian Aid" 0 0 0 0
2 "Angola" 2019 "Eco Infra & Services" 0 0 0 0
5 "Angola" 2019 "Social Infra & Services" 692115801.1534774 6.160e+08 0 0



As you can see years and oecd_sector are not unique for each country. For each country we can have aid in different sectors. I want to reshape the data into wide form by these oecd_sectors such that these 5 sectors become 5 variables and for each each country I have unique years with corresponding amount committed against these sectors. I tried few possibilities but could not get the desired results. Will be grateful if some guidance can be provided please
regards,

[Clyde Schechter]

The major obstacle is that the names of the sectors are very long and also contain characters that are illegal in variable names. So that variable has to be modified so that it can be incorporated into the names of the new variables to be created. This is made even harder by the long names of your Amount* variables--which would require truncating the sectors so short as to be unrecognizable. So I've compromised by reducing the Amount* and YAV* variables to two characters. I also note that the variable id is redundant--it is just a numeric code for the oecd_sector variable, so I'm eliminating it.

replace oecd_sector = substr(strtoname(oecd_sector), 1, 30)
rename AmountConstantUSD2021 AC
rename AmountNominalUSD AN
rename YAV3 Y3
rename YAVN YN
drop id

reshape wide AC AN Y3 YN, i(Recipient CommitmentYear) j(oecd_sector) string

[Muhammad Saleh]

Dear Clyde, it worked perfectly now as you suggested. Truly thankful
regards,