Reshape

[Philmon Amasalu]

Hi all,

I am having difficulty reshaping a monthly data on many items listed on the columns. I have amonth and year variable on the column as well.
The data is neither long nor wide.
I have read many posts about reshape but non is related to mine (example here https://www.statalist.org/forums/for...-of-many-years

One possible solution I often use is to set my directory to the original data
and keep only the month year and one of the items, save it as item1. Finally, to append all items to get a long data, but this is not effieicnet here since I have 80 plus columns and many years.

A sample data looks like the following

* Example generated by -dataex-. For more info, type help dataex
clear
input int year byte month double(A B C D E)
1980  1         100         100         100   100         100
1980  2 108.5000076         104 101.2000046 101.2 99.09999847
1980  3         109 109.2000046 101.9000092 101.6 99.09999847
1980  4 107.7000046 112.0999985 109.7000046 102.4        99.5
1980  5 107.4000015 113.7999954 110.6000061 102.3        99.5
1980  6 107.1999969 117.7000046 111.3000031 102.8  99.6000061
1980  7 107.3000031 119.7999954 116.6000061 102.1 98.09999847
1980  8       107.5 121.5999985 119.5000076 102.6 98.70000458
1980  9 107.5999985 122.0999985 121.3000031 103.9 98.79999542
1980 10       107.5 124.7000046 124.8000031   106 98.29999542
1980 11 107.8000031 128.8000031 126.9000092 106.8 97.40000153
1980 12 108.1000061 131.7000122 129.3000031 103.7 99.09999847
1981  1 108.3000031 134.8000031 133.4000092 105.3 102.7000046
1981  2 116.8000031 137.4000092 135.5000153   107 103.8999939
1981  3 118.9000015         140 137.1000061 108.5 113.4999924
1981  4 119.2000046 142.1000061 144.2000122 114.7 119.1000061
1981  5 123.1000061 144.4000092 143.6000061 116.5 119.9999924
1981  6 123.3000031 148.3000031       144.5   119 121.4999924
1981  7 123.4000015 149.9000092 147.3000031 119.2 118.5999985
1981  8 123.3000031 151.1000061 147.6000061 119.8 116.8999939
1981  9 123.2000046 151.4000092 147.4000092 121.3 119.5999985
1981 10 123.4000015 151.8000031 147.1000061 121.5 118.6999969
1981 11 122.0999985 150.8000031 147.1000061   122         121
1981 12 128.4000092 152.8000031 147.4000092 123.5 123.6999969
1982  1 129.3000031 153.1000061 149.6000061 124.7 131.6999969
1982  2 129.6999969 154.6000061 151.0000153 125.7 132.6999969
1982  3 129.6000061 154.7000122 151.9000092 127.5 133.1999969
1982  4 135.3000031 157.3000031       155.5 128.9 136.1999969
1982  5 136.0999908       159.5 155.6000061   131 139.6000061
1982  6         136 159.4000092 155.2000122 133.5 146.5999908
1982  7       136.5 160.7000122 158.4000092 134.9 148.0999908
1982  8 143.8000031 163.2000122         159 138.1         153
1982  9 144.8000031 164.7000122 158.9000092   140         157
1982 10 151.6000061 166.2000122 163.7000122 142.6 164.1000061
1982 11 153.1999969 167.3000031 165.3000183   142         164
1982 12 153.6999969 175.3000031 166.9000092   146 164.8999939
1983  1 159.3000031 179.1000061 172.7000122 147.7 167.6000061
1983  2 159.1000061 182.9000092 173.3000031 148.7 165.7000122
1983  3 163.3000031 186.1000061 173.6000061 149.8 163.8000031
1983  4 163.6000061 189.8000031 179.1000061 151.5         163
1983  5 168.6999969 196.1000061 182.3000031 152.2 165.0999908
1983  6 168.3000031 199.1000061 182.9000092 152.2 168.8000031
1983  7 168.2000122       201.5 190.9000092 152.1 171.3000031
1983  8         173 204.6000061 193.0000153   153 171.7999878
1983  9 174.1999969 206.1999969 195.1000214 153.9 172.3999939
1983 10 176.3000031 205.1999969 199.1000061 153.9         172
1983 11 176.3999939 206.4000092 199.1999969 154.1 171.1999969
1983 12 187.1999969 208.8000031 200.1000061 154.7 171.6999969
1984  1 188.6999969 211.1999969 200.7000275 154.4 174.0999908
1984  2       188.5 213.3000031 203.3000183 155.4 177.1000061
1984  3         189         215 204.4000092   157 179.6999969
1984  4 200.6000061 217.3000031 208.3000183 158.6 185.6000061
1984  5 200.9000092 219.6999969 208.7000122 160.4 190.9999847
1984  6 201.2000122 220.9000092 208.7000122 162.2 195.8999939
1984  7 201.9000092 222.5000153 209.8000183 164.7 199.5999908
1984  8 204.4000092 223.3000031 209.9000092 166.3       200.5
1984  9       204.5 225.0000153 210.0000153 167.1 202.4000092
1984 10 204.7000122 226.3000031 211.4000092   167 202.4000092
1984 11           .           .           .     .           .
1984 12 205.5999908 229.0999908 211.9000092   168 202.5999908
1985  1 211.8000031 231.6999969       213.5 169.2 203.1999969
1985  2         214 232.5999908 216.5000153   171         212
1985  3 213.8999939 232.9000092 219.2000122 172.2 212.2000122
1985  4 214.3999939 235.4000092 219.6000061 173.2 212.0999908
1985  5 214.3999939 237.9000092       220.5 174.2 211.6999969
1985  6 215.6000061 239.9000092 220.8000031 176.6 216.6000061
1985  7 225.0999908 242.4000092 221.4000092 180.3 228.6999969
1985  8 226.3000031 244.4000092 229.4000092 184.2 234.1000061
1985  9 226.9000092 248.5000153 233.0000153 186.3 234.3999939
1985 10 227.8000031 252.1999969 233.8000183 187.9 234.8000031
1985 11 230.4000092 256.7000122 239.5000153 189.8 233.6999969
1985 12 231.6000061       265.5 239.8000183 190.1         234
end

I would appreciate advices on how to go about this data. Thank you in Advance!

[Clyde Schechter]

//    CREATE A REAL STATA MONTHLY DATE VARIABLE
gen int mdate = ym(year, month), before(year)
assert missing(mdate) == missing(year, month)
format mdate %tm

ds mdate year month, not
rename (`r(varlist)') x=

reshape long x, i(mdate) j(item) string

Note: The creation of the Stata monthly variable mdate is not strictly necessary for the purpose of reshaping the data. You could skip that part and use -i(year month)- in the -reshape- command instead of -i(mdate)-. But it is very likely that whatever you ultimately do in analyzing this data will require, or at least be made easier by, having a real Stata monthly date variable.

The -reshape- itself is not really very complicated. The only problem is the need to have a common prefix before the item name. If the numbers in the A, B, C, ... columns represented prices, instead of x, I would have used price or pr, or something like that. I chose x because you don't say what these numbers are, so I cannot guess an appropriate name. You can modify the code to use something more suitable to your circumstances (or just -rename- the x variable after it runs.)

[Philmon Amasalu]

Dear Clyde,

Thnak you very much for your helpful advice. I am sorry, I missed to mention that the values correspond to prices, as you jsut said.

This worked flawlessly for my data. Thank you very much once again!