Help changing data format 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

[Andrew Musau]

You need 2 reshapes.

* 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

drop ioid
ds year io*, not
rename (`r(varlist)') (val=)
reshape long val, i(ioname year) j(country) string
reshape wide val, i(country year) j(ioname) string
rename val* *

Res.:

. l, sep(0)

     +--------------------------------------------------------+
     |     country   year   AALCO   AMU   ASEAN   AU   Andean |
     |--------------------------------------------------------|
  1. | Afghanistan   2014       0     0       0    0        0 |
  2. | Afghanistan   2015       0     0       0    0        0 |
  3. | Afghanistan   2016       0     0       0    0        0 |
  4. | Afghanistan   2017       0     0       0    0        0 |
  5. |     Albania   2014       0     0       0    0        0 |
  6. |     Albania   2015       0     0       0    0        0 |
  7. |     Albania   2016       0     0       0    0        0 |
  8. |     Albania   2017       0     0       0    0        0 |
  9. |     Algeria   2014       0     1       0    1        0 |
 10. |     Algeria   2015       0     1       0    1        0 |
 11. |     Algeria   2016       0     1       0    1        0 |
 12. |     Algeria   2017       0     1       0    1        0 |
 13. |      Angola   2014       0     0       0    1        0 |
 14. |      Angola   2015       0     0       0    1        0 |
 15. |      Angola   2016       0     0       0    1        0 |
 16. |      Angola   2017       0     0       0    1        0 |
     +--------------------------------------------------------+

[Andrea Stephenson]

That worked really well, thank you Andrew. I really appreciate the help!