Re: st: Wide to Panel

[David Kantor <kantor.d@att.net>]

At 08:24 AM 10/17/2006, Alice Dobson wrote:
> Hi all,
> I have a datafile containing an unabalanced panel of firms for a period of
> around 10 years. This file contains data on around five variables. I have
> procured additional data on around 15 firm-level variables for these set of
> firms. However, the problem is that each variable is in a different excel
> file and that too in a wide format. I can transfer these files into stata
> format using stat transfer. Could anyone please tell me if its possible to
> convert these multiple data files into one datafile in the panel format?
>
> Each individual file for a single variable looks like this. There are 15
> such files, one for each varible. The numberf of firms is too large and
> hence will take a lot of time to do by hand.
>
> FIRM     1985    1986   1987   1988   1989   1990   1991   1992   1993  1994
>
> FIRM1   100      123      124     3001     293   324   512        413
> FIRM2
> FIRM3
> FIRM4
> :
> :
> :
> :
> FIRM1002
>
>
> Best,
> Alice
Start by Stat/Transferring these into Stata.

But your column names of  "1985", "1986" , etc. will get rendered as, 
I believe, "_1985", "_1986", etc..

Each of those needs to be further renamed to have a common prefix 
that is distinct for the given table.  Presumably, each table is one 
variable, so the prefix should correspond to that variable.  Thus, if 
the table is "number of employees" you should rename the variables 
something like
 nemp1985, nemp1986, etc.
If your table is about revenue, then name them rev1985, rev1985, etc.

(You could, alternatively, change the column names in the Excel 
tables.  But in any case, for the transfer to go smoothly, the Excel 
tables need to be "clean" -- have just the column names in the first 
row, and the data in all subsequent rows.)

Do this for each table.
Then you will want to merge all the tables together.

Presumably you also want to -reshape wide-.  That could come before 
or after the merge.

Hope this helps.
--David

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/