st: Re: Wide to Panel

["Michael Blasnik" <michael.blasnik@verizon.net>]

You can potentially do this in a loop using Stata's built-in odbc drivers. 
Stat/Transfer would also work, but you'd have to check how it names the 
columns that start with a number.  I found that when using ODBC these 
columns are named F2, F2, F4,...

You would need to get a list of the files you want to import into a local 
macro (perhaps using fs or the dir extended macro function).  It would 
probably be useful if the files were named for the variable they contain or 
else you could create a parallel list macro that contains those names.  I'll 
assume this latter case.  I have hard coded 1985 as the starting year but if 
it varies across datafiles you would need to create a list to change the 
initialization of the local year in the loop.  Anyway, here's a first cut:

local files "myfile1.xls myfile2.xls myfile3.xls"
local varnames "myvar1 myvar2 myvar3"
local i=1

foreach file of local files {
odbc load, dsn("Excel Files;DBQ=`file'") table("Sheet1$") dialog(complete)
unab vars: _all
local vars: list vars - firm
local var1: word 1 of `vars'
local vars: list vars - var1

local year=1985
local j=2
foreach var of local vars {
rename F`j++' v`year++'
}
reshape long v, i(firm) j(year)
local vname: word `i++' of `varnames'
rename v `vname'

sort firm
if `i'==1 save allvars
else  merge firm using allvars, _merge(mrg`vname')

}

Michael Blasnik

----- Original Message ----- 
From: "ALICE DOBSON" <alice_dobson@hotmail.com>
To: <statalist@hsphsun2.harvard.edu>
Sent: Tuesday, October 17, 2006 8:24 AM
Subject: st: Wide to Panel



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