Statalist The Stata Listserver

[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

st: Re: Wide to Panel

From   "Michael Blasnik" <>
To   <>
Subject   st: Re: Wide to Panel
Date   Tue, 17 Oct 2006 09:57:42 -0400

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" <>
To: <>
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
*   For searches and help try:

© Copyright 1996–2017 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index