Creating a stacked panel data from data in wide format

[Titir Bhattacharya]

Hi all,
Please consider the following example data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 date double(ABC PQR MNO)
"Jan 1, 2006" .4486 .5262 .5685
"Feb 1, 2006" .4567 .5663 .5378
"Mar 1, 2006" .4984 .6433 .6688
"Apr 1, 2006" .6146 .7553 .7955
"May 1, 2006" .7285 .8042 .8098
"Jun 1, 2006" .7064 .8099 .7791
"Jul 1, 2006" .6293 .7576 .7684
"Aug 1, 2006" .7783 .7061 .7408
"Sep 1, 2006" .7286    .7  .748
"Oct 1, 2006" .6737 .7003 .7141
"Nov 1, 2006" .6684 .7515 .7423
"Dec 1, 2006" .6984 .7764 .7609
"Jan 1, 2007"  .703 .7564 .7394
"Feb 1, 2007" .6762 .6957 .7234
"Mar 1, 2007" .6188  .662 .7172
"Apr 1, 2007" .5778 .6664 .7512
"May 1, 2007" .6596 .7607 .7924
"Jun 1, 2007" .7271 .8039 .8044
"Jul 1, 2007"  .781 .7824 .7997
"Aug 1, 2007" .7631 .7248 .8112
"Sep 1, 2007" .7205 .7189  .737
"Oct 1, 2007" .7122 .7331 .8031
"Nov 1, 2007" .7279 .7046 .7261
"Dec 1, 2007" .5741 .6608 .7102
"Jan 1, 2008" .4897 .5772 .6217
"Feb 1, 2008" .4184  .532 .6141
"Mar 1, 2008" .3787 .5305 .6206
"Apr 1, 2008" .5193  .646 .7145
"May 1, 2008" .6536 .7724 .7935
"Jun 1, 2008" .6581 .7907 .7892
"Jul 1, 2008"  .699 .7726  .777
"Aug 1, 2008" .7085 .7537 .7528
"Sep 1, 2008" .7181 .7395 .7541
"Oct 1, 2008" .7283  .712 .7642
"Nov 1, 2008"  .725  .737 .8176
"Dec 1, 2008" .6455 .7084 .7325
"Jan 1, 2009"  .488 .5947 .6755
"Feb 1, 2009" .4657 .6318 .6496
"Mar 1, 2009" .3986 .5417 .6611
"Apr 1, 2009" .4573 .6494 .7455
"May 1, 2009" .6335 .7518  .761
"Jun 1, 2009" .6873 .7552 .7612
"Jul 1, 2009" .6537  .748 .7483
"Aug 1, 2009" .7249 .7396 .7549
"Sep 1, 2009" .6942 .7293 .7286
"Oct 1, 2009" .6197 .7418 .7215
"Nov 1, 2009"  .665 .7017 .7607
"Dec 1, 2009" .6119 .6873 .7324
"Jan 1, 2010" .6493 .7209 .7127
"Feb 1, 2010" .5878 .6309 .6795
"Mar 1, 2010" .6507 .6847 .7222
"Apr 1, 2010" .6401 .7183  .737
"May 1, 2010"  .716 .7671 .7895
"Jun 1, 2010"  .746 .7823 .7792
"Jul 1, 2010" .7533 .7351 .7376
"Aug 1, 2010" .7265 .7325 .7534
"Sep 1, 2010" .6878  .706 .7311
"Oct 1, 2010" .7533 .7293 .7827
"Nov 1, 2010" .6598 .7526 .7467
"Dec 1, 2010" .5842 .6808 .7212
"Jan 1, 2011"  .503 .6785 .7217
"Feb 1, 2011" .3953 .6274 .6958
"Mar 1, 2011" .3846 .5651 .6879
"Apr 1, 2011" .4782 .6452 .7343
"May 1, 2011" .6236 .7476 .7986
"Jun 1, 2011" .7141 .8104 .7965
"Jul 1, 2011" .7323 .7752 .7453
"Aug 1, 2011" .7029 .7648 .7592
"Sep 1, 2011" .7322 .7149 .7872
"Oct 1, 2011" .6492 .7087 .7507
"Nov 1, 2011" .7633 .7383 .7837
"Dec 1, 2011" .7084 .7464 .7684
"Jan 1, 2012"  .624 .6478  .717
"Feb 1, 2012" .4738 .5162 .5822
"Mar 1, 2012" .4122  .503 .5378
"Apr 1, 2012" .4641  .576 .6818
"May 1, 2012" .6656 .7429 .8114
"Jun 1, 2012" .6867 .7754 .8121
"Jul 1, 2012" .6984 .7732 .7922
"Aug 1, 2012" .7645  .773 .7641
"Sep 1, 2012"  .758 .7062  .736
"Oct 1, 2012" .7258 .7169 .7381
"Nov 1, 2012" .6529 .7293 .7726
"Dec 1, 2012" .7046 .7485 .7685
"Jan 1, 2013" .7045 .7385 .7651
"Feb 1, 2013" .6341 .7045 .7252
"Mar 1, 2013" .5233 .6399 .6625
"Apr 1, 2013" .6747 .6821 .7895
"May 1, 2013" .7273 .7206 .8051
"Jun 1, 2013"  .694 .7396 .7672
"Jul 1, 2013" .7177 .7493 .7532
"Aug 1, 2013" .7374 .7507 .7551
"Sep 1, 2013" .7797 .7584 .7623
"Oct 1, 2013" .7167 .7221 .7855
"Nov 1, 2013" .7656 .7831 .7856
"Dec 1, 2013" .6956 .7419 .7552
"Jan 1, 2014" .6412 .6956 .7064
"Feb 1, 2014" .5519 .6186 .6641
"Mar 1, 2014" .5621 .5463  .634
"Apr 1, 2014" .6137 .6753 .7303
end

I want to transform this into panel format with the following structure:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 date str3 district
"month1,year1" "ABC"
"month2,year1" "ABC"
"month1,year1" "PQR"
"month2,year1" "PQR"
end

I tried the following:

reshape long ABC PQR MNO, i(date) j(district)

and got the following error:

no xij variables found
    You typed something like reshape wide a b, i(i) j(j).
    reshape looked for existing variables named a# and b# but could not find any. Remember this picture:

         long                                wide
        +---------------+                   +------------------+
        | i   j   a   b |                   | i   a1 a2  b1 b2 |
        |---------------| <--- reshape ---> |------------------|
        | 1   1   1   2 |                   | 1   1   3   2  4 |
        | 1   2   3   4 |                   | 2   5   7   6  8 |
        | 2   1   5   6 |                   +------------------+
        | 2   2   7   8 |
        +---------------+

        long to wide: reshape wide a b, i(i) j(j)    (j existing variable)
        wide to long: reshape long a b, i(i) j(j)    (j new variable)

I next tried

stack ABC PQR MNO, into(var) clear

gen district = _stack

tostring district,replace
replace district = "ABC" if district == "1"
replace district = "PQR" if district == "2"
replace district = "MNO" if district == "3"

This however drops the date variable.
I need help with the following:
1. within each district, generating a month of the year series such that it goes "Month1, Year1; Month2,Year2..." starting from "Month1,Year0"
2. In the complete data, it is impossible to manually replace the district names so ideally the code should generate district/_stack variable based on the string expressions.
3. If there is any simpler way to solve the whole problem, that would be great too.

Thanks!

[Andrew Musau]

rename (ABC PQR MNO) value=
reshape long value, i(date) j(district) string
gen mdate= daily(date, "MDY")
replace mdate= ym(year(mdate), month(mdate))
format mdate %tm
encode district, gen(District)
xtset District mdate

Res:

. xtset District mdate

Panel variable: District (strongly balanced)
 Time variable: mdate, 2006m1 to 2014m4
         Delta: 1 month

. l in 80/120, sepby(D)

     +-----------------------------------------------------+
     |        date   district   value     mdate   District |
     |-----------------------------------------------------|
 80. | Aug 1, 2012        ABC   .7645    2012m8        ABC |
 81. | Sep 1, 2012        ABC    .758    2012m9        ABC |
 82. | Oct 1, 2012        ABC   .7258   2012m10        ABC |
 83. | Nov 1, 2012        ABC   .6529   2012m11        ABC |
 84. | Dec 1, 2012        ABC   .7046   2012m12        ABC |
 85. | Jan 1, 2013        ABC   .7045    2013m1        ABC |
 86. | Feb 1, 2013        ABC   .6341    2013m2        ABC |
 87. | Mar 1, 2013        ABC   .5233    2013m3        ABC |
 88. | Apr 1, 2013        ABC   .6747    2013m4        ABC |
 89. | May 1, 2013        ABC   .7273    2013m5        ABC |
 90. | Jun 1, 2013        ABC    .694    2013m6        ABC |
 91. | Jul 1, 2013        ABC   .7177    2013m7        ABC |
 92. | Aug 1, 2013        ABC   .7374    2013m8        ABC |
 93. | Sep 1, 2013        ABC   .7797    2013m9        ABC |
 94. | Oct 1, 2013        ABC   .7167   2013m10        ABC |
 95. | Nov 1, 2013        ABC   .7656   2013m11        ABC |
 96. | Dec 1, 2013        ABC   .6956   2013m12        ABC |
 97. | Jan 1, 2014        ABC   .6412    2014m1        ABC |
 98. | Feb 1, 2014        ABC   .5519    2014m2        ABC |
 99. | Mar 1, 2014        ABC   .5621    2014m3        ABC |
100. | Apr 1, 2014        ABC   .6137    2014m4        ABC |
     |-----------------------------------------------------|
101. | Jan 1, 2006        MNO   .5685    2006m1        MNO |
102. | Feb 1, 2006        MNO   .5378    2006m2        MNO |
103. | Mar 1, 2006        MNO   .6688    2006m3        MNO |
104. | Apr 1, 2006        MNO   .7955    2006m4        MNO |
105. | May 1, 2006        MNO   .8098    2006m5        MNO |
106. | Jun 1, 2006        MNO   .7791    2006m6        MNO |
107. | Jul 1, 2006        MNO   .7684    2006m7        MNO |
108. | Aug 1, 2006        MNO   .7408    2006m8        MNO |
109. | Sep 1, 2006        MNO    .748    2006m9        MNO |
110. | Oct 1, 2006        MNO   .7141   2006m10        MNO |
111. | Nov 1, 2006        MNO   .7423   2006m11        MNO |
112. | Dec 1, 2006        MNO   .7609   2006m12        MNO |
113. | Jan 1, 2007        MNO   .7394    2007m1        MNO |
114. | Feb 1, 2007        MNO   .7234    2007m2        MNO |
115. | Mar 1, 2007        MNO   .7172    2007m3        MNO |
116. | Apr 1, 2007        MNO   .7512    2007m4        MNO |
117. | May 1, 2007        MNO   .7924    2007m5        MNO |
118. | Jun 1, 2007        MNO   .8044    2007m6        MNO |
119. | Jul 1, 2007        MNO   .7997    2007m7        MNO |
120. | Aug 1, 2007        MNO   .8112    2007m8        MNO |
     +-----------------------------------------------------+

.

[Nick Cox]

Andrew Musau solved the problem nicely. But what was wrong in #1?

reshape long requires stubnames (prefixes) with the syntax you uses, not full variable names. If there is no shared prefix, you need to prepend one such.

stack requires all variables you want stacked to be named. You needed three copies of date to be stacked alongside your others.