Reshaping data from organisation year to country year

[Andrea Stephenson]

Hello everyone,

I have been struggling with converting my data from one format to another and I was hoping someone here would be able to at least hint at a solution. I have tried the reshape command (to no avail) but I really don't think that it is very useful here.

I have data that records yearly country membership in an international institution in the following format:

* Example generated by -dataex-. For more info, type help dataex
clear

input int year float ioid str11 ioname float(Afghanistan Albania Algeria Angola)
2014  24 "AU"      0 0 1 1
2015  24 "AU"      0 0 1 1
2016  24 "AU"      0 0 1 1
2017  24 "AU"      0 0 1 1
2014  26 "Andean"  0 0 0 0
2015  26 "Andean"  0 0 0 0
2016  26 "Andean"  0 0 0 0
2017  26 "Andean"  0 0 0 0
2014  17 "AMU"     0 0 1 0
2015  17 "AMU"     0 0 1 0
2016  17 "AMU"     0 0 1 0
2017  17 "AMU"     0 0 1 0
2014   2 "AALCO"   0 0 0 0
2015   2 "AALCO"   0 0 0 0
2016   2 "AALCO"   0 0 0 0
2017   2 "AALCO"   0 0 0 0
2014  23 "ASEAN"   0 0 0 0
2015  23 "ASEAN"   0 0 0 0
2016  23 "ASEAN"   0 0 0 0
2017  23 "ASEAN"   0 0 0 0
end

But I need it converted to the following format:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID year) str11 Country float(AU Andean AMU AALCO ASEAN)
1 2014 "Afghanistan" 0 0 0 0 0
1 2015 "Afghanistan" 0 0 0 0 0
1 2016 "Afghanistan" 0 0 0 0 0
1 2017 "Afghanistan" 0 0 0 0 0
2 2014 "Albania"     0 0 0 0 0
2 2015 "Albania"     0 0 0 0 0
2 2016 "Albania"     0 0 0 0 0
2 2017 "Albania"     0 0 0 0 0
3 2014 "Algeria"     1 0 1 0 0
3 2015 "Algeria"     1 0 1 0 0
3 2016 "Algeria"     1 0 1 0 0
3 2017 "Algeria"     1 0 1 0 0
4 2014 "Angola"      1 0 0 0 0
4 2015 "Angola"      1 0 0 0 0
4 2016 "Angola"      1 0 0 0 0
4 2017 "Angola"      1 0 0 0 0
end

I would really appreciate all and any help you can give me.

Best wishes,

Andrea

[Frode Andre]

You should post your questions on this forum: General - Statalist

This - Sandbox - is a "forum to make test posts, practice with the formatting features of the forum software, etc"