st: reshape dataset with date as identifier

[Adrian Stork <storkadrian@googlemail.com>]

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/