RE: st: Transposing datasets

["Nick Cox" <n.j.cox@durham.ac.uk>]

Thanks to Neil for the mention of -renvars- by Jeroen Weesie and myself. 

However, -renvars- is to be downloaded from the Stata Journal site, not SSC. 

If people are unclear about where my stuff may be found, a package -njc_stuff-, which _is_ to be found at SSC, is dedicated to that purpose. 

Nick 
n.j.cox@durham.ac.uk 

Neil Shephard replied to Kaspar Dardas

> 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.

It's 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.


*
*   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/