Dear all,

I am trying to bring a dataset into the right format. However, I can´t figure out the last step of reshaping the data. Right now, the dataset looks as follow:
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year double(sh sp) str2(Country perc)
2006 .             .0861 "AE" "40"
2006 .             .5543 "AE" "90"
2006 .             .1694 "AE" "99"
2006 .             .1236 "AF" "40"
2006 .              .435 "AF" "90"
2006 .             .1632 "AF" "99"
2006 .             .1329 "AL" "40"
2006 .             .3136 "AL" "90"
2006 .             .0763 "AL" "99"
2006 .             .1332 "AM" "40"
2006 .             .3399 "AM" "90"
2006 .             .1052 "AM" "99"
2006 . .0790999999999999 "AO" "40"
2006 .              .511 "AO" "90"
2006 .             .1685 "AO" "99"
2006 .             .0716 "AR" "40"
2006 .              .505 "AR" "90"
2006 .             .2523 "AR" "99"
2006 .             .1554 "AT" "40"
2006 .             .3663 "AT" "90"
2006 .             .1301 "AT" "99"
2006 .             .1324 "AU" "40"
2006 .              .315 "AU" "90"
2006 .             .1199 "AU" "99"
2006 .             .1332 "AZ" "40"
2006 .             .3399 "AZ" "90"
2006 .             .1052 "AZ" "99"
2006 .             .1376 "BA" "40"
2006 .             .3192 "BA" "90"
2006 .             .0779 "BA" "99"
2006 .              .134 "BD" "40"
2006 .             .4346 "BD" "90"
2006 .             .1569 "BD" "99"
2006 .             .1418 "BE" "40"
2006 .             .3223 "BE" "90"
2006 .             .0873 "BE" "99"
2006 .             .0923 "BF" "40"
2006 .              .501 "BF" "90"
2006 .             .1685 "BF" "99"
2006 .             .1209 "BG" "40"
2006 .             .3484 "BG" "90"
2006 .             .1087 "BG" "99"
2006 .             .1144 "BH" "40"
2006 .             .5154 "BH" "90"
2006 .             .1812 "BH" "99"
2006 .              .122 "BI" "40"
2006 .             .4425 "BI" "90"
2006 .             .1261 "BI" "99"
2006 .             .0966 "BJ" "40"
2006 .             .4936 "BJ" "90"
2006 .             .1577 "BJ" "99"
2006 .                 . "BN" "40"
2006 .                 . "BN" "90"
2006 .                 . "BN" "99"
2006 .             .0624 "BO" "40"
2006 .             .5303 "BO" "90"
2006 .              .227 "BO" "99"
2006 .             .0694 "BR" "40"
2006 .             .5557 "BR" "90"
2006 .             .2554 "BR" "99"
2006 .             .0624 "BS" "40"
2006 .             .5303 "BS" "90"
2006 .              .227 "BS" "99"
2006 .             .1108 "BT" "40"
2006 .             .4493 "BT" "90"
2006 .             .1513 "BT" "99"
2006 .             .0375 "BW" "40"
2006 .             .6805 "BW" "90"
2006 .             .2865 "BW" "99"
2006 .             .1332 "BY" "40"
2006 .             .3399 "BY" "90"
2006 .             .1052 "BY" "99"
2006 .             .0624 "BZ" "40"
2006 .             .5303 "BZ" "90"
2006 .              .227 "BZ" "99"
2006 .             .1375 "CA" "40"
2006 .             .4003 "CA" "90"
2006 .             .1554 "CA" "99"
2006 .             .0813 "CD" "40"
2006 .             .5194 "CD" "90"
2006 .             .1842 "CD" "99"
2006 .             .0638 "CF" "40"
2006 .             .5856 "CF" "90"
2006 .             .2443 "CF" "99"
2006 .             .0723 "CG" "40"
2006 .             .5434 "CG" "90"
2006 .             .1875 "CG" "99"
2006 .             .1601 "CH" "40"
2006 .             .3454 "CH" "90"
2006 .             .1327 "CH" "99"
2006 .                 . "CI" "40"
2006 .                 . "CI" "90"
2006 .                 . "CI" "99"
2006 .             .0524 "CL" "40"
2006 .              .616 "CL" "90"
2006 .             .2963 "CL" "99"
2006 .             .0865 "CM" "40"
2006 .             .5002 "CM" "90"
2006 .             .1607 "CM" "99"
2006 .             .0981 "CN" "40"
end
Please note that it is just a coincidence that variable sh contains only missing values in this example.

My goal is to have the following variables: Year, Country, and sh and sp for each percentile (so, sh40, sh90, sh99, sp40, sp90, sp99). I tried
Code:
gen id = _n
reshape wide sp sh, i(id) j(perc, string)
However, I then get the actual value and several missings for the same Country-Year observation.

Is there a way to get to a point where I just have one observation per variable for each Country-Year pair?

I´m looking forward to any helpful comments.
Best,
Niko