st: RE: reshape dataset with date as identifier

[Joe Canner <jcanner1@jhmi.edu>]

Adrian,

Try adding the -string- option to your reshape command, since your suffix has non-numeric characters.

Regards,
Joe Canner
Johns Hopkins University School of Medicine
________________________________________
From: owner-statalist@hsphsun2.harvard.edu [owner-statalist@hsphsun2.harvard.edu] on behalf of Adrian Stork [storkadrian@googlemail.com]
Sent: Thursday, January 02, 2014 12:31 PM
To: statalist@hsphsun2.harvard.edu
Subject: st: reshape dataset with date as identifier

Dear all

First of all: happy new year!

Second :-)

I wanted to reshape a matrix of 312 rows x 61 columns where the 312
rows are 312 months from 1987 to 2012 and the 61 columns are portfolio
returns
The dataset loos like this  and looks like this:

date    equal_nonl_01_01        equal_nonl_01_02        equal_nonl_01_03        equal_nonl_01_04        equal_nonl_01_05        equal_nonl_02_01
   equal_nonl_02_02 .    etc.
1987m1  .       .       .       .       .       .       .       .       .       .
1987m2  .       .       .       .       .       .       .       .       .       .
1987m3  .       .       .       .       .       .       .       .       .       .
1987m4  .       .       .       .       .       .       .       .       .       .
1987m5  .       .       .       .       .       .       .       .       .       .
1987m6  .       .       .       .       .       .       .       .       .       .
1987m7  .02     .03     .02     -.04    .09     .       .       .       .       .
1987m8  .01     -.06    .03     -.03    .03     .       .       .       .       .
1987m9  -.03    -.045   -.01    -.06    .06     .       .       .       .       .
1987m10 -.20    -.24    -.23    -.07    .08     .       .       .       .       .
1987m11 -.05    -.09    -.03    -.08    .02     .       .       .       .       .
1987m12 .04     .08     .02     -.03    .04     .       .       .       .       .
1988m1  .11     .02     .05     -.04    .09     .       .       .       .       .
1988m2  .08     .03     .04     -.06    .05     .       .       .       .       .
1988m3  -.03    .03     .02     -.03    .04     .       .       .       .       .
1988m4  .03     .09     .76     -.05    .02     .       .       .       .       .
1988m5  -.02    -.04    -.01    -.06    .04     0.2     .03     .02     -.04    .09
1988m6  .09     .04     .06     .03     .03     0.25    .02     -.02    -.04    .05
1988m7  -.03    -.09    .04     .02     -.02    -.05    -.04    -.01    -.02    .06
1988m8  -.06    -.01    -.01    -.04    .03     -.03    .01     -.01    -.01    .08
1988m9  .03     .02     .03     .01     .06     .03     .06     .01     -.04    .09

The stub for all variables is "equal_nonl_" and the suffix "xx_xx"
idenfies each column. Now I want to reshape this matrix where in the
end three colums should be left. One for date, one for the portfolio
identifier "00_00" and one for the return of each portfolio at the
respective date which should look like this:

date             id                 return
1987m1  01_01   .09
1987m3  01_01   .06
1987m4  01_01   .03
1987m5  01_01   .04
1987m6  01_01   .01
1987m7  02_01   .03
1987m8  02_01   -.06
1987m9  02_01   -.045
1987m10 02_01   -.24
... etc

I'm not sure how to handle the reshape command here, especially
because of the identifiers in the name itself and the date figures as
"id". I tried the following command:

.reshape long equal_nonl_ , i(date)

but I get back the error message:

"_ j contains all missing values"

Does anyone know how to reshape this dataset before I turn to Excel in
complete desperation?

Best,
Adrian
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/