Reshaping data to Long

[Aamina Khurraa]

Reshaping data to long

Today, 21:27
Hi, I normally transform the wide data to a long format in Excel using the picot chart, but this time my excel data exceeds the grid limit, hence I am forced to transform the wide data to a long format in Stata. I have 846 firms (listed in one column), each having daily observation of stock prices (the only variable) from 2009 to 2022. Now the dates are in the horizontal titles of the prices for each firm. I have tried every possible way, but it is not converting my data to a long format and gives the error 111. I have also added a prefix yr with all the yearly variable names but all in vain. Please help me reshape the data. Please see the data example:

Code:
* Example generated by -dataex-. For more info, type help dataex clear input long ISIN double(yrB yrC yrD yrE yrF yrG yrH yrI yrJ yrK yrL) 1 10.5757 10.6276 10.6972 10.9154 10.899 10.7216 10.626 10.242 9.8111 10.0432 9.4895 2 2.5279 2.5587 2.4213 2.3901 2.415 2.396 2.4527 2.3101 2.4468 2.3879 2.2788 6 6.3501 6.2723 6.2742 6.2454 6.3415 6.3112 6.2179 6.0221 5.7482 5.6273 5.5463 5 7.569 7.5916 7.4782 7.6153 7.6851 7.3934 7.2397 6.9593 6.6514 6.7455 6.5765 8 1.3595 1.3514 1.3094 1.2789 1.2739 1.2355 1.2085 1.1125 1.0455 .9839 .9421 7 20.0403 20.4951 20.353 20.9412 20.6077 20.1701 20.1848 19.2832 18.8044 18.6662 17.8325 10 2.6804 2.7201 2.6589 2.7185 2.6737 2.6738 2.6325 2.5643 2.5471 2.6236 2.5175 12 . . . . . . . . . . . 13 . . . . . . . . . . . 4 13.8158 13.6693 13.7438 14.0695 14.1961 14.0642 14.0507 13.4167 12.8679 12.9605 12.2178 14 .4799 .4638 .4702 .4759 .4704 .473 .4756 .456 .4495 .4574 .4431 15 17.9945 18.0657 17.8159 18.5756 19.0804 18.5327 18.462 17.6215 17.0017 17.0372 16.7408 16 . . . . . . . . . . . 18 5.649 5.7111 5.6593 5.8302 6.0682 5.7944 5.8115 5.7649 5.55 5.669 5.4333 19 11.7514 11.7474 11.8254 12.0848 11.9922 11.6862 11.5482 11.1786 10.9469 10.9862 10.5374 11 . . . . . . . . . . . 17 15.41 15.6 15.81 15.79 15.82 15.69 15.57 15.13 14.72 14.59 14.51 21 7.7889 7.6633 7.5978 7.5833 7.4611 7.5867 7.4511 7.4111 7.5311 7.3622 7.4433 22 22.1606 22.1868 22.4023 23.0622 22.435 22.8662 22.337 22.3239 22.6898 21.7294 21.7229 24 11.4 11.29 11.75 11.79 11 12.46 11.32 11.53 11.5 10.22 9.95 25 29.12 29.86 29.76 30.03 28.04 27.97 27.41 26.81 28.1 27.15 27.77 26 . . . . . . . . . . . 28 . . . . . . . . . . . 30 49.96 50.63 51.99 53.02 51.6 50.85 49.76 49.7 49.43 46.56 45.88 31 59.14 64.95 67.51 73.59 73.21 76.41 72.24 64.65 61.74 59.55 58.69 32 . . . . . . . . . . . 33 76.14 76.54 75.46 76.49 74.42 75.1 74.08 72.77 73.47 70.94 72.28 34 5.5 5.55 5.54 5.29 5.55 5.49 5.94 5.56 5.25 5.26 5.22 35 . . . . . . . . . . . 36 5.37 5.48 5.5 5.61 5.79 5.77 5.82 5.69 5.52 5.49 5.46 37 3.13 3.27 3.96 4.09 3.88 3.95 4.02 3.95 4.05 4 4.21 39 1 1.01 1.01 1.02 1.05 1.06 1.06 1.03 1.01 1.01 1.01 40 51.56 51 50.18 51.95 51.14 50.35 50.06 48.95 49.83 49.27 50.61 20 . . . . . . . . . . . 41 . . . . . . . . . . . 44 267.1099 263.3999 275.6399 286 264.0999 274.9399 275 271.99 285.45 260 262 42 . . . . . . . . . . . 43 . . . . . . . . . . . 45 3.7 3.89 3.75 4.08 4.2 4.33 4.3 3.71 3.76 3.45 3.12 46 1.98 2.03 2.13 2.46 2.41 2.32 2.36 2.18 2.17 2.05 2.07 48 2.84 3.15 3.16 3.38 3.2 3.16 3.08 2.94 2.85 2.61 2.67 47 6.29 6.47 6.67 7.3 7.05 6.8 6.91 6.78 6.57 5.97 6.31 51 2.4 2.48 2.41 2.58 2.51 2.48 2.59 2.48 2.5 2.29 2.26 50 1.04 1.09 1.16 1.36 1.33 1.46 1.54 1.49 1.41 1.35 1.41 56 . . . . . . . . . . . 49 . . . . . . . . . . . 52 . . . . . . . . . . . 53 3.86 4.3 4.37 4.71 4.39 4.36 4.29 4.07 3.95 3.56 3.6 54 2.8 2.86 2.93 3.09 3.05 3.09 3.11 3.06 3.07 2.98 2.95 55 . . . . . . . . . . . 57 1.51 1.52 1.6 1.68 1.65 1.66 1.63 1.6 1.63 1.58 1.56 58 25.31 26.45 27.39 29.4 28.52 28.62 28.16 27.25 27.26 26.33 25.77 59 26.98 27.38 27.82 28.53 28.3 28.4 27.67 25.97 25.86 24.65 24.79 60 20.69 21.34 22.07 23.37 22.75 22.91 22.08 21.06 20.63 19.65 19.18 61 10.03 10.7 10.92 11.77 11.36 11.33 10.73 10.19 10.1 9.89 9.69 62 . . . . . . . . . . . 63 364.52 370.28 378.85 381.71 378.51 380.07 377.3 373.72 366.66 361.88 355.77 64 315.92 312.27 305.63 325.63 315.93 311.41 311.02 314.68 311.66 309.49 309.79 65 16.77 17.08 17.76 18.62 18.17 18.15 18.03 17.34 17.19 16.57 16.58 67 18.88 19.42 20.09 21.5 20.36 20.27 20.58 19.84 18.76 18.02 17.63 68 25.61 27.11 28.85 29.14 28.36 27.62 26.86 26.41 26.62 25.45 25.73 69 27.95 29.09 27.4 27.72 27.04 27.16 26.24 25.58 25.99 24.74 24.83 70 16.85 17.26 19.14 20.45 19.2 19.81 19.28 18.89 18.74 17.88 18.13 71 12.68 13.02 13.53 14.42 13.94 14.54 14.21 14.12 14.14 13.37 13.02 72 18.16 18.72 19.03 19.64 18.93 19 18.53 17.71 18.05 17.29 17.65 23 . . . . . . . . . . . 73 29.24 30.4 31.33 32.27 31.21 31.84 30.86 29.68 29.17 28.02 26.91 74 23.04 23.65 24.18 25.4 24.54 24.63 24.37 23.03 22.37 21.28 21.39 75 17.6 18.14 19.08 19.75 19.16 19.57 19.28 18.63 18.53 17.69 17.41 76 . . . . . . . . . . . 77 . . . . . . . . . . . 66 14.05 14.45 15.03 15.27 15.06 15.11 15.08 14.87 14.63 14.27 14.06 115 . . . . . . . . . . . 113 .52 .52 .54 .55 .55 .53 .54 .55 .54 .55 .57 141 . . . . . . . . . . . 136 . . . . . . . . . . . 102 .42 .42 .43 .43 .43 .42 .42 .42 .41 .42 .42 119 . . . . . . . . . . . 108 .61 .61 .65 .66 .65 .63 .65 .65 .65 .67 .67 130 . . . . . . . . . . . 147 . . . . . . . . . . . 132 . . . . . . . . . . . 131 . . . . . . . . . . . 139 . . . . . . . . . . . 151 . . . . . . . . . . . 112 .35 .35 .37 .37 .37 .36 .37 .37 .36 .38 .38 111 .61 .61 .63 .64 .63 .61 .62 .63 .62 .64 .64 124 . . . . . . . . . . . 129 . . . . . . . . . . . 146 . . . . . . . . . . . 127 . . . . . . . . . . . 143 . . . . . . . . . . . 125 . . . . . . . . . . . 148 . . . . . . . . . . . 107 .54 .54 .55 .56 .55 .53 .54 .54 .53 .55 .54 109 .532 .566 .582 .557 .508 .485 .49 .467 .462 .475 .471 116 . . . . . . . . . . . 104 2.73 2.73 2.84 2.91 2.91 2.8 2.83 2.76 2.73 2.87 2.8 100 1.28 1.28 1.32 1.37 1.35 1.28 1.3 1.29 1.26 1.34 1.31 99 .35 .35 .35 .36 .36 .35 .35 .35 .34 .36 .36 end label values ISIN ISIN2 label def ISIN2 1 "AU000000ANZ3", modify label def ISIN2 2 "AU000000AUB9", modify label def ISIN2 4 "AU000000B4", modify label def ISIN2 5 "AU000000BEN6", modify label def ISIN2 6 "AU000000BOQ8", modify label def ISIN2 7 "AU000000CBA7", modify label def ISIN2 8 "AU000000CGF5", modify label def ISIN2 10 "AU000000IAG3", modify label def ISIN2 11 "AU000000ISU6", modify label def ISIN2 12 "AU000000JLG8", modify label def ISIN2 13 "AU000000MPL3", modify label def ISIN2 14 "AU000000NHF0", modify label def ISIN2 15 "AU000000QBE9", modify label def ISIN2 16 "AU000000SDF8", modify label def ISIN2 17 "AU000000SGB0", modify label def ISIN2 18 "AU000000SUN6", modify label def ISIN2 19 "AU000000WBC1", modify label def ISIN2 20 "BM78669Q1007", modify label def ISIN2 21 "BMG0450A1053", modify label def ISIN2 22 "BMG0464B1072", modify label def ISIN2 23 "BMG053841547", modify label def ISIN2 24 "BMG0585R1060", modify label def ISIN2 25 "BMG0692U1099", modify label def ISIN2 26 "BMG0772R2087", modify label def ISIN2 28 "BMG1190F1077", modify label def ISIN2 30 "BMG2519Y1084", modify label def ISIN2 31 "BMG3075P1014", modify label def ISIN2 32 "BMG3198U1027", modify label def ISIN2 33 "BMG3223R1088", modify label def ISIN2 34 "BMG4593F1389", modify label def ISIN2 35 "BMG5005R1079", modify label def ISIN2 36 "BMG5361W1047", modify label def ISIN2 37 "BMG5753U1128", modify label def ISIN2 39 "BMG7371X1065", modify label def ISIN2 40 "BMG7496G1033", modify label def ISIN2 41 "BMG8192H1060", modify label def ISIN2 42 "BMG8196D1011", modify label def ISIN2 43 "BMG947871015", modify label def ISIN2 44 "BMG9618E1075", modify label def ISIN2 45 "BMG982941046", modify label def ISIN2 46 "BRABCBACNPR4", modify label def ISIN2 47 "BRBBASACNOR3", modify label def ISIN2 48 "BRBBDCACNPR8", modify label def ISIN2 49 "BRBBSEACNOR5", modify label def ISIN2 50 "BRBPACNPR1", modify label def ISIN2 51 "BRBRSRACNPB4", modify label def ISIN2 52 "BRIRBRACNOR4", modify label def ISIN2 53 "BRITUBACNPR1", modify label def ISIN2 54 "BRPSSAACNOR7", modify label def ISIN2 55 "BRQUALACNOR6", modify label def ISIN2 56 "BRSANBCDAM13", modify label def ISIN2 57 "BRSULACDAM12", modify label def ISIN2 58 "CA0636711016", modify label def ISIN2 59 "CA0641491075", modify label def ISIN2 60 "CA1360691010", modify label def ISIN2 61 "CA13677F1018", modify label def ISIN2 62 "CA24477T1003", modify label def ISIN2 63 "CA2685751075", modify label def ISIN2 64 "CA3039011026", modify label def ISIN2 65 "CA39138C1068", modify label def ISIN2 66 "CA40427H5096", modify label def ISIN2 67 "CA45075E1043", modify label def ISIN2 68 "CA45823T1066", modify label def ISIN2 69 "CA51925D1069", modify label def ISIN2 70 "CA56501R1064", modify label def ISIN2 71 "CA6330671034", modify label def ISIN2 72 "CA7392391016", modify label def ISIN2 73 "CA7800871021", modify label def ISIN2 74 "CA8667961053", modify label def ISIN2 75 "CA8911605092", modify label def ISIN2 76 "CA89679A2092", modify label def ISIN2 77 "CA92512J1066", modify label def ISIN2 99 "CNE0000015Y0", modify label def ISIN2 100 "CNE000001B33", modify label def ISIN2 102 "CNE000001N05", modify label def ISIN2 104 "CNE000001Q93", modify label def ISIN2 107 "CNE1000000R4", modify label def ISIN2 108 "CNE1000000S2", modify label def ISIN2 109 "CNE1000002H1", modify label def ISIN2 111 "CNE1000005P7", modify label def ISIN2 112 "CNE100000627", modify label def ISIN2 113 "CNE100000734", modify label def ISIN2 115 "CNE100000RJ0", modify label def ISIN2 116 "CNE100000SL4", modify label def ISIN2 119 "CNE100001QN2", modify label def ISIN2 124 "CNE100002391", modify label def ISIN2 125 "CNE1000023P0", modify label def ISIN2 127 "CNE100002623", modify label def ISIN2 129 "CNE100002FM5", modify label def ISIN2 130 "CNE100002FX2", modify label def ISIN2 131 "CNE100002G76", modify label def ISIN2 132 "CNE100002GQ4", modify label def ISIN2 136 "CNE100002SN6", modify label def ISIN2 139 "CNE1000031G2", modify label def ISIN2 141 "CNE100003F50", modify label def ISIN2 143 "CNE100003JB8", modify label def ISIN2 146 "CNE100003LQ2", modify label def ISIN2 147 "CNE100003PT7", modify label def ISIN2 148 "CNE100003YB7", modify label def ISIN2 151 "CNE1000056L9", modify

[Maarten Buis]

As you can see your example is unreadable. Can you post it again, but copy everything dataex returns? Also give us the exact commands you tried.

[Nick Cox]

I can't see any reshape code to comment on and this data example is mangled (because the CODE markers were omitted). Nevertheless the problem looks like a standard one.

* Example generated by -dataex-. For more info, type help dataex 
clear 
input long ISIN double(yrB yrC yrD yrE yrF yrG yrH yrI yrJ yrK yrL) 
1 10.5757 10.6276 10.6972 10.9154 10.899 10.7216 10.626 10.242 9.8111 10.0432 9.4895 
2 2.5279 2.5587 2.4213 2.3901 2.415 2.396 2.4527 2.3101 2.4468 2.3879 2.2788 
6 6.3501 6.2723 6.2742 6.2454 6.3415 6.3112 6.2179 6.0221 5.7482 5.6273 5.5463 
5 7.569 7.5916 7.4782 7.6153 7.6851 7.3934 7.2397 6.9593 6.6514 6.7455 6.5765 
8 1.3595 1.3514 1.3094 1.2789 1.2739 1.2355 1.2085 1.1125 1.0455 .9839 .9421 
7 20.0403 20.4951 20.353 20.9412 20.6077 20.1701 20.1848 19.2832 18.8044 18.6662 17.8325 
10 2.6804 2.7201 2.6589 2.7185 2.6737 2.6738 2.6325 2.5643 2.5471 2.6236 2.5175 
12 . . . . . . . . . . . 
13 . . . . . . . . . . . 
4 13.8158 13.6693 13.7438 14.0695 14.1961 14.0642 14.0507 13.4167 12.8679 12.9605 12.2178 
end 
label values ISIN ISIN2 
label def ISIN2 1 "AU000000ANZ3", modify 
label def ISIN2 2 "AU000000AUB9", modify 
label def ISIN2 4 "AU000000B4", modify 
label def ISIN2 5 "AU000000BEN6", modify 
label def ISIN2 6 "AU000000BOQ8", modify 
label def ISIN2 7 "AU000000CBA7", modify 
label def ISIN2 8 "AU000000CGF5", modify 
label def ISIN2 10 "AU000000IAG3", modify 
label def ISIN2 11 "AU000000ISU6", modify 
label def ISIN2 12 "AU000000JLG8", modify 
label def ISIN2 13 "AU000000MPL3", modify 

reshape long yr, i(ISIN) j(which) string 
encode which, gen(year)
replace year = year + 2008 
rename yr price 

list, sepby(ISIN)


    +---------------------------------------+
     |         ISIN   which     price   year |
     |---------------------------------------|
  1. | AU000000ANZ3       B   10.5757   2009 |
  2. | AU000000ANZ3       C   10.6276   2010 |
  3. | AU000000ANZ3       D   10.6972   2011 |
  4. | AU000000ANZ3       E   10.9154   2012 |
  5. | AU000000ANZ3       F    10.899   2013 |
  6. | AU000000ANZ3       G   10.7216   2014 |
  7. | AU000000ANZ3       H    10.626   2015 |
  8. | AU000000ANZ3       I    10.242   2016 |
  9. | AU000000ANZ3       J    9.8111   2017 |
 10. | AU000000ANZ3       K   10.0432   2018 |
 11. | AU000000ANZ3       L    9.4895   2019 |
     |---------------------------------------|
 12. | AU000000AUB9       B    2.5279   2009 |
 13. | AU000000AUB9       C    2.5587   2010 |
 14. | AU000000AUB9       D    2.4213   2011 |
 15. | AU000000AUB9       E    2.3901   2012 |
 16. | AU000000AUB9       F     2.415   2013 |
 17. | AU000000AUB9       G     2.396   2014 |
 18. | AU000000AUB9       H    2.4527   2015 |
 19. | AU000000AUB9       I    2.3101   2016 |
 20. | AU000000AUB9       J    2.4468   2017 |
 21. | AU000000AUB9       K    2.3879   2018 |
 22. | AU000000AUB9       L    2.2788   2019 |
     |---------------------------------------|
 23. |   AU000000B4       B   13.8158   2009 |
 24. |   AU000000B4       C   13.6693   2010 |
 25. |   AU000000B4       D   13.7438   2011 |
 26. |   AU000000B4       E   14.0695   2012 |
 27. |   AU000000B4       F   14.1961   2013 |
 28. |   AU000000B4       G   14.0642   2014 |
 29. |   AU000000B4       H   14.0507   2015 |
 30. |   AU000000B4       I   13.4167   2016 |
 31. |   AU000000B4       J   12.8679   2017 |
 32. |   AU000000B4       K   12.9605   2018 |
 33. |   AU000000B4       L   12.2178   2019 |
     |---------------------------------------|
 34. | AU000000BEN6       B     7.569   2009 |
 35. | AU000000BEN6       C    7.5916   2010 |
 36. | AU000000BEN6       D    7.4782   2011 |
 37. | AU000000BEN6       E    7.6153   2012 |
 38. | AU000000BEN6       F    7.6851   2013 |
 39. | AU000000BEN6       G    7.3934   2014 |
 40. | AU000000BEN6       H    7.2397   2015 |
 41. | AU000000BEN6       I    6.9593   2016 |
 42. | AU000000BEN6       J    6.6514   2017 |
 43. | AU000000BEN6       K    6.7455   2018 |
 44. | AU000000BEN6       L    6.5765   2019 |
     |---------------------------------------|
 45. | AU000000BOQ8       B    6.3501   2009 |
 46. | AU000000BOQ8       C    6.2723   2010 |
 47. | AU000000BOQ8       D    6.2742   2011 |
 48. | AU000000BOQ8       E    6.2454   2012 |
 49. | AU000000BOQ8       F    6.3415   2013 |
 50. | AU000000BOQ8       G    6.3112   2014 |
 51. | AU000000BOQ8       H    6.2179   2015 |
 52. | AU000000BOQ8       I    6.0221   2016 |
 53. | AU000000BOQ8       J    5.7482   2017 |
 54. | AU000000BOQ8       K    5.6273   2018 |
 55. | AU000000BOQ8       L    5.5463   2019 |
     |---------------------------------------|
 56. | AU000000CBA7       B   20.0403   2009 |
 57. | AU000000CBA7       C   20.4951   2010 |
 58. | AU000000CBA7       D    20.353   2011 |
 59. | AU000000CBA7       E   20.9412   2012 |
 60. | AU000000CBA7       F   20.6077   2013 |
 61. | AU000000CBA7       G   20.1701   2014 |
 62. | AU000000CBA7       H   20.1848   2015 |
 63. | AU000000CBA7       I   19.2832   2016 |
 64. | AU000000CBA7       J   18.8044   2017 |
 65. | AU000000CBA7       K   18.6662   2018 |
 66. | AU000000CBA7       L   17.8325   2019 |
     |---------------------------------------|
 67. | AU000000CGF5       B    1.3595   2009 |
 68. | AU000000CGF5       C    1.3514   2010 |
 69. | AU000000CGF5       D    1.3094   2011 |
 70. | AU000000CGF5       E    1.2789   2012 |
 71. | AU000000CGF5       F    1.2739   2013 |
 72. | AU000000CGF5       G    1.2355   2014 |
 73. | AU000000CGF5       H    1.2085   2015 |
 74. | AU000000CGF5       I    1.1125   2016 |
 75. | AU000000CGF5       J    1.0455   2017 |
 76. | AU000000CGF5       K     .9839   2018 |
 77. | AU000000CGF5       L     .9421   2019 |
     |---------------------------------------|
 78. | AU000000IAG3       B    2.6804   2009 |
 79. | AU000000IAG3       C    2.7201   2010 |
 80. | AU000000IAG3       D    2.6589   2011 |
 81. | AU000000IAG3       E    2.7185   2012 |
 82. | AU000000IAG3       F    2.6737   2013 |
 83. | AU000000IAG3       G    2.6738   2014 |
 84. | AU000000IAG3       H    2.6325   2015 |
 85. | AU000000IAG3       I    2.5643   2016 |
 86. | AU000000IAG3       J    2.5471   2017 |
 87. | AU000000IAG3       K    2.6236   2018 |
 88. | AU000000IAG3       L    2.5175   2019 |
     |---------------------------------------|
 89. | AU000000JLG8       B         .   2009 |
 90. | AU000000JLG8       C         .   2010 |
 91. | AU000000JLG8       D         .   2011 |
 92. | AU000000JLG8       E         .   2012 |
 93. | AU000000JLG8       F         .   2013 |
 94. | AU000000JLG8       G         .   2014 |
 95. | AU000000JLG8       H         .   2015 |
 96. | AU000000JLG8       I         .   2016 |
 97. | AU000000JLG8       J         .   2017 |
 98. | AU000000JLG8       K         .   2018 |
 99. | AU000000JLG8       L         .   2019 |
     |---------------------------------------|
100. | AU000000MPL3       B         .   2009 |
101. | AU000000MPL3       C         .   2010 |
102. | AU000000MPL3       D         .   2011 |
103. | AU000000MPL3       E         .   2012 |
104. | AU000000MPL3       F         .   2013 |
105. | AU000000MPL3       G         .   2014 |
106. | AU000000MPL3       H         .   2015 |
107. | AU000000MPL3       I         .   2016 |
108. | AU000000MPL3       J         .   2017 |
109. | AU000000MPL3       K         .   2018 |
110. | AU000000MPL3       L         .   2019 |
     +---------------------------------------+

[Aamina Khurraa]

Dear Nick,

Many thanks for your reply and managing to work around my dataex example.

The issue that I am facing with this code is that although I have added a prefix 'yr' with all my date variables, but in reality their label is daily date starting from 01/01/2009 to 30/12/2022. So when we gen new variable which, it does not take the label dates rather just the title of the variables.

When I imported data from Excel, all the dates row was replaced by these alphabetics B, C, D... and dates are shown in their labels. Now how is it possible to get these dates back to the column date (or year in your example). Following codes are used:

reshape long yr, i(ISIN) j(which) string
encode which, gen(date)
replace date = date + 2008
rename yr Price

Please see the dataex of my data now after using your code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 which double Price long(date ISIN)
"AA"   7.9721 2009 1
"AAA" 20.7078 2010 1
"AAB"  19.682 2011 1
"AAC" 19.7796 2012 1
"AAD" 19.2368 2013 1
"AAE" 19.8123 2014 1
"AAF"  20.372 2015 1
"AAG" 19.6156 2016 1
"AAH" 19.7414 2017 1
"AAI" 19.6206 2018 1
"AAJ" 18.4327 2019 1
"AAK" 18.2256 2020 1
"AAL" 18.1084 2021 1
"AAM" 19.5797 2022 1
"AAN" 19.3734 2023 1
"AAO" 19.0752 2024 1
"AAP" 18.8212 2025 1
"AAQ" 18.0523 2026 1
"AAR" 17.7715 2027 1
"AAS" 18.2726 2028 1
"AAT" 19.2561 2029 1
"AAU" 20.2294 2030 1
"AAV"  20.798 2031 1
"AAW" 20.9424 2032 1
"AAX" 21.2842 2033 1
"AAY"  21.193 2034 1
"AAZ" 21.4883 2035 1
"AB"   8.1672 2036 1
"ABA" 21.7195 2037 1
"ABB" 21.3037 2038 1
"ABC" 21.7772 2039 1
"ABD" 21.3579 2040 1
"ABE"  21.745 2041 1
"ABF" 22.4661 2042 1
"ABG" 22.3368 2043 1
"ABH"  22.142 2044 1
"ABI" 23.3147 2045 1
"ABJ" 23.4835 2046 1
"ABK" 22.8197 2047 1
"ABL" 21.8289 2048 1
"ABM" 21.4894 2049 1
"ABN" 21.0419 2050 1
"ABO"  21.601 2051 1
"ABP" 21.7382 2052 1
"ABQ" 22.1421 2053 1
"ABR" 21.9805 2054 1
"ABS" 20.5621 2055 1
"ABT" 21.0641 2056 1
"ABU" 20.9241 2057 1
"ABV" 20.8592 2058 1
"ABW"  20.585 2059 1
"ABX" 20.5498 2060 1
"ABY" 20.0096 2061 1
"ABZ" 19.2276 2062 1
"AC"   8.4485 2063 1
"ACA" 19.0505 2064 1
"ACB" 18.3538 2065 1
"ACC" 18.3288 2066 1
"ACD" 18.1628 2067 1
"ACE" 18.9233 2068 1
"ACF" 19.3748 2069 1
"ACG" 20.2815 2070 1
"ACH" 20.7871 2071 1
"ACI" 21.2568 2072 1
"ACJ" 21.4944 2073 1
"ACK" 21.2767 2074 1
"ACL" 21.6187 2075 1
"ACM" 21.4086 2076 1
"ACN" 21.0419 2077 1
"ACO" 20.9654 2078 1
"ACP" 20.7474 2079 1
"ACQ"  20.585 2080 1
"ACR" 20.4596 2081 1
"ACS" 20.7443 2082 1
"ACT" 20.3399 2083 1
"ACU" 20.4058 2084 1
"ACV" 20.8433 2085 1
"ACW"  20.656 2086 1
"ACX" 20.9985 2087 1
"ACY" 20.9985 2088 1
"ACZ" 21.0088 2089 1
"AD"   8.2767 2090 1
"ADA"  20.787 2091 1
"ADB" 20.7229 2092 1
"ADC" 20.8808 2093 1
"ADD" 20.8808 2094 1
"ADE" 21.2947 2095 1
"ADF" 21.5444 2096 1
"ADG" 21.0825 2097 1
"ADH" 20.7446 2098 1
"ADI" 20.8368 2099 1
"ADJ" 21.2641 2100 1
"ADK" 21.5077 2101 1
"ADL" 21.5818 2102 1
"ADM" 21.6301 2103 1
"ADN" 21.3776 2104 1
"ADO" 21.7098 2105 1
"ADP"   21.42 2106 1
"ADQ" 21.5153 2107 1
"ADR" 21.6666 2108 1
end
label values date date
label def date 2009 "CWH", modify
label def date 2010 "CWI", modify
label def date 2011 "CWJ", modify
label def date 2012 "CWK", modify
label def date 2013 "CWL", modify
label def date 2014 "CWM", modify
label def date 2015 "CWN", modify
label def date 2016 "CWO", modify
label def date 2017 "CWP", modify
label def date 2018 "CWQ", modify
label def date 2019 "CWR", modify
label def date 2020 "CWS", modify
label def date 2021 "CWT", modify
label def date 2022 "CWU", modify
label def date 2023 "CWV", modify
label def date 2024 "CWW", modify
label def date 2025 "CWX", modify
label def date 2026 "CWY", modify
label def date 2027 "CWZ", modify
label def date 2028 "CX", modify
label def date 2029 "CXA", modify
label def date 2030 "CXB", modify
label def date 2031 "CXC", modify
label def date 2032 "CXD", modify
label def date 2033 "CXE", modify
label def date 2034 "CXF", modify
label def date 2035 "CXG", modify
label def date 2036 "CXH", modify
label def date 2037 "CXI", modify
label def date 2038 "CXJ", modify
label def date 2039 "CXK", modify
label def date 2040 "CXL", modify
label def date 2041 "CXM", modify
label def date 2042 "CXN", modify
label def date 2043 "CXO", modify
label def date 2044 "CXP", modify
label def date 2045 "CXQ", modify
label def date 2046 "CXR", modify
label def date 2047 "CXS", modify
label def date 2048 "CXT", modify
label def date 2049 "CXU", modify
label def date 2050 "CXV", modify
label def date 2051 "CXW", modify
label def date 2052 "CXX", modify
label def date 2053 "CXY", modify
label def date 2054 "CXZ", modify
label def date 2055 "CY", modify
label def date 2056 "CYA", modify
label def date 2057 "CYB", modify
label def date 2058 "CYC", modify
label def date 2059 "CYD", modify
label def date 2060 "CYE", modify
label def date 2061 "CYF", modify
label def date 2062 "CYG", modify
label def date 2063 "CYH", modify
label def date 2064 "CYI", modify
label def date 2065 "CYJ", modify
label def date 2066 "CYK", modify
label def date 2067 "CYL", modify
label def date 2068 "CYM", modify
label def date 2069 "CYN", modify
label def date 2070 "CYO", modify
label def date 2071 "CYP", modify
label def date 2072 "CYQ", modify
label def date 2073 "CYR", modify
label def date 2074 "CYS", modify
label def date 2075 "CYT", modify
label def date 2076 "CYU", modify
label def date 2077 "CYV", modify
label def date 2078 "CYW", modify
label def date 2079 "CYX", modify
label def date 2080 "CYY", modify
label def date 2081 "CYZ", modify
label def date 2082 "CZ", modify
label def date 2083 "CZA", modify
label def date 2084 "CZB", modify
label def date 2085 "CZC", modify
label def date 2086 "CZD", modify
label def date 2087 "CZE", modify
label def date 2088 "CZF", modify
label def date 2089 "CZG", modify
label def date 2090 "CZH", modify
label def date 2091 "CZI", modify
label def date 2092 "CZJ", modify
label def date 2093 "CZK", modify
label def date 2094 "CZL", modify
label def date 2095 "CZM", modify
label def date 2096 "CZN", modify
label def date 2097 "CZO", modify
label def date 2098 "CZP", modify
label def date 2099 "CZQ", modify
label def date 2100 "CZR", modify
label def date 2101 "CZS", modify
label def date 2102 "CZT", modify
label def date 2103 "CZU", modify
label def date 2104 "CZV", modify
label def date 2105 "CZW", modify
label def date 2106 "CZX", modify
label def date 2107 "CZY", modify
label def date 2108 "CZZ", modify
label values ISIN ISIN2
label def ISIN2 1 "AU000000ANZ3", modify

[Nick Cox]

The solution in #3 is just indicative and needs adaptation for daily dates. Without being able to test this code, as there is not a different data example, you may get closer with

reshape long yr, i(ISIN) j(which) string
gen ndigits = strlen(which) 
egen group = group(ndigits which) 
encode group, gen(date)
replace date = date + mdy(12, 31, 2008) 
rename yr Price

This is on the assumption that your price variable names run B ... Z AA...AZ BA...BZ ....

but that may be wrong.

And do you really have variables for all weekend days and public holidays?

It's hard to suggest good code without good data examples and really detailed explanations.

[Aamina Khurraa]

Hi Nick,

I am also linking my excel file for your access so you can see the dates.

[Aamina Khurraa]

Reply on #3:

The price variable names are in the similar manner as you suggested.

The data is only for weekdays and not weekends. something like as below:

01/01/2009

02/01/2009

05/01/2009

06/01/2009

07/01/2009

08/01/2009

09/01/2009

12/01/2009

13/01/2009

When I use the below code in #5

 
 encode group, gen(date)

It gives error, saying not possible with numeric variable, hence cannot get the date format right.

Best,
Aamina

[Clyde Schechter]

I think the problem begins with the way the data is imported to Stata. If the "variable names" in the spreadsheet are things like 01/01/2009, etc., then I think what you have to do is import the data in such a way that these values are in the first observation of the data. If you are using -import excel- that would be done by omitting the -firstrow- option. If you are using -import delimited-, specify the -varnames(nonames)- option.

Then I would do something like this:

// CREATE A SUITABLE PART OF A VARIABLE NAME IN OBS 1
// AND RENAME THE VARIABLES WITH THESE, PREFIXED BY pr
ds ISIN, not
foreach v of varlist `r(varlist)' {
    replace `v' = strtoname(`v') in 1
    rename `v' pr`=`v'[1]'
}

// GET RID OF OBS 1
drop in 1

// RESHAPE LONG
reshape long pr, i(ISIN) j(sdate) string

// CLEAN UP sdate SO IT CAN BE CONVERTED TO A STATA DATE VARIABLE
replace sdate = subinstr(substr(sdate, 2, .), "_", "/", .)

// CONVERT TO STATA DATE VARIABLE
gen date = daily(var1, "MDY")
format date %td
drop sdate

// CONVERT pr TO A NUMERIC VARIABLE
destring pr, replace

[Nick Cox]

Sorry, #5 was pretty confused and I had a long meeting later. Clyde Schechter is in the lead now.