Reshaping data to long format

[yilmaz kemal]

Hello,

I have monthly tourist arrivals data from Canada and Mexico to the USA. The columns D to AR represent sequential monthly dates from January 2000 to January 2002. I was not able to convert this data into long form since the columns do not include any date info. Any suggestion to convert this data into a long format?


Here is a very short version of my data. Actual data includes many countries and spans a much longer period.

* Example generated by -dataex-. For more info, type help dataex
clear
input str47 region long(D E F G H I J AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR)
"Canada" 937669 933472 1383065 1362891 1257535 1081729 1731130 726370 828134 762028 1218383 1105711 1102359 1009878 1520851 1675807 1144492 1001179 818992 836539 865217 772230 1145924 981729 1006245
"Mexico" 234000 221000  270000  464000  276000  291000  574000 391000 214000 215000  439000  235000  259000  297000  571000  374000  256000  239000 255000 492000 234000 188000  212000 381000  266000
end

Many thanks in advance,

[Nick Cox]

The problem yields to using the information you have after a rename. (It's possibly easier than this if you can use the renumber() option of rename.)

* Example generated by -dataex-. For more info, type help dataex
clear
input str47 region long(D E F G H I J AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR)
"Canada" 937669 933472 1383065 1362891 1257535 1081729 1731130 726370 828134 762028 1218383 1105711 1102359 1009878 1520851 1675807 1144492 1001179 818992 836539 865217 772230 1145924 981729 1006245
"Mexico" 234000 221000  270000  464000  276000  291000  574000 391000 214000 215000  439000  235000  259000  297000  571000  374000  256000  239000 255000 492000 234000 188000  212000 381000  266000
end


rename (D-AR) value#, addnumber 

reshape long value, i(region) j(mdate)
replace mdate = mdate + ym(1999, 12) 
format mdate %tm 

list, sepby(region)

     +----------------------------+
     | region     mdate     value |
     |----------------------------|
  1. | Canada    2000m1    937669 |
  2. | Canada    2000m2    933472 |
  3. | Canada    2000m3   1383065 |
  4. | Canada    2000m4   1362891 |
  5. | Canada    2000m5   1257535 |
  6. | Canada    2000m6   1081729 |
  7. | Canada    2000m7   1731130 |
  8. | Canada    2000m8    726370 |
  9. | Canada    2000m9    828134 |
 10. | Canada   2000m10    762028 |
 11. | Canada   2000m11   1218383 |
 12. | Canada   2000m12   1105711 |
 13. | Canada    2001m1   1102359 |
 14. | Canada    2001m2   1009878 |
 15. | Canada    2001m3   1520851 |
 16. | Canada    2001m4   1675807 |
 17. | Canada    2001m5   1144492 |
 18. | Canada    2001m6   1001179 |
 19. | Canada    2001m7    818992 |
 20. | Canada    2001m8    836539 |
 21. | Canada    2001m9    865217 |
 22. | Canada   2001m10    772230 |
 23. | Canada   2001m11   1145924 |
 24. | Canada   2001m12    981729 |
 25. | Canada    2002m1   1006245 |
     |----------------------------|
 26. | Mexico    2000m1    234000 |
 27. | Mexico    2000m2    221000 |
 28. | Mexico    2000m3    270000 |
 29. | Mexico    2000m4    464000 |
 30. | Mexico    2000m5    276000 |
 31. | Mexico    2000m6    291000 |
 32. | Mexico    2000m7    574000 |
 33. | Mexico    2000m8    391000 |
 34. | Mexico    2000m9    214000 |
 35. | Mexico   2000m10    215000 |
 36. | Mexico   2000m11    439000 |
 37. | Mexico   2000m12    235000 |
 38. | Mexico    2001m1    259000 |
 39. | Mexico    2001m2    297000 |
 40. | Mexico    2001m3    571000 |
 41. | Mexico    2001m4    374000 |
 42. | Mexico    2001m5    256000 |
 43. | Mexico    2001m6    239000 |
 44. | Mexico    2001m7    255000 |
 45. | Mexico    2001m8    492000 |
 46. | Mexico    2001m9    234000 |
 47. | Mexico   2001m10    188000 |
 48. | Mexico   2001m11    212000 |
 49. | Mexico   2001m12    381000 |
 50. | Mexico    2002m1    266000 |
     +----------------------------+

[yilmaz kemal]

Thanks a lot Nick Cox . I knew the rename command but did not know using it with value#, addnumber, which does a good job.