Re: st: Transposing datasets

[Neil Shephard <nshephard@gmail.com>]

On Mon, Aug 2, 2010 at 11:37 AM, Kaspar Dardas
<kaspar.dardas@students.ebs.edu> wrote:
> I have a dataset with about 32000 observations, which is in long
> format (see structure below). gvkey is the identifier for a firm
> (about 600 different firms), datadate is the monthend value between
> 2002 and 2010, which of course repeats in the dataset (again, long
> format) and mcap_sum is my observation, which is different for each
> month and gvkey.
>
> gvkey   datadate        mcap_sum
> 212782  30jun2005       4946.9
> 212782  31jul2005       5042.1
> 212782  31aug2005       5145
> 212782  30sep2005       5302.5
> 212782  31oct2005       5253.5
> 212782  30nov2005       5642.7
> 212782  31dec2005       6230
> etc...
>
> Well, I would like to transpose my dataset so it shows each month as a
> variable and the observations are mcap_sums. My tries with reshape
> failed miserably.

Its useful and informative to post the code that "failed miserably"
and the output that is associated with it, that way people can see
what you've been trying and point out where you've gone wrong (all
recommended as good practice in the Statalist FAQ, linked from the
bottom of every message to the list).

In the absence of this I'd suggest

reshape wide mcap_sum, i(gvkey) j(datadate)

...but this may be complicated by the format of 'datadate' if it is
formatted as a date variable (as underneath the formatting which
presents it as you've listed it will be stored as a numeric of sorts).

> (xpose wont work because I still want to keep
> mcap_sum as an observation).. Does anybody has a suggestion to solve
> this quickly?
>
> gvkey    31dec2005   30nov2005   31oct2005
> 212782  6230              5642.7           5253.5   ...........

You'll never achieve these names as Stata variables are not allowed to
start with numbers.  -renvars- (install with -ssc install renvars-)
may be of use in renaming variables here though.

And whats urgent for you isn't likely to be for anyone else on the list ;-)

Neil

-- 
"... no scientific worker has a fixed level of significance at which
from year to year, and in all circumstances, he rejects hypotheses; he
rather gives his mind to each particular case in the light of his
evidence and his ideas." - Sir Ronald A. Fisher (1956)

Email - nshephard@gmail.com
Website - http://slack.ser.man.ac.uk/
Photos - http://www.flickr.com/photos/slackline/

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