Creating panel out of excel files

[Nick Bas]

Hi all,

Not my first choice of format, but I've got excel files of the following format for years 2002-2012. How can I most efficiently/effectively combine them into a panel?

They all look like this (you can ignore/drop the lrname variable):

state_name	Male	Female	Total	share	lrname	year
ANDHRA PRADESH	2218	448	2666	19.7	Andhra Pradesh	2004
ARUNACHAL PRADESH	16	4	20	25.3	Arunachal Pradesh	2004
ASSAM	316	15	331	11.7	Assam	2004
BIHAR	23	0	23	6.6	Bihar	2004
CHHATTISGARH	1213	182	1395	31	Chhattisgarh	2004
GOA	7	1	8	2.5	Goa	2004
GUJARAT	452	71	523	11	Gujarat	2004
HARYANA	144	16	160	7.7	Haryana	2004
HIMACHAL PRADESH	43	9	52	14	Himachal Pradesh	2004
JAMMU AND KASHMIR	1	0	1	0.9	Jammu and Kashmir	2004
JHARKHAND	21	0	21	5	Jharkhand	2004

And each excel file takes the path of e.g. Users/myname/documents/paper/data/dataset_2004.xlsx
i.e. the file for 2002 is dataset_2002.xlsx, the file for 2003 is dataset_2003.xlsx and so on.

Importing individually into stata, I use: sheet("sheet1") firstrow case(lower)

At the end of this, I would ideally have a panel spanning 2002-2012, which I would then be merging to another panel of different variables for 2002-2012 for the same state_name values (I'll cross that bridge when I come to it)

[Mike Lacy]

While you may run into various little problems with inconsistencies among the data files, something like the following will work:

clear
save "allyears.dta", emptyok
forval y = 2002/2012 {
  import excel using "Users/myname/documents/paper/data/dataset_`y'.xlsx" ///
     firstrow case(lower) sheet("sheet1") clear
  append using "allyears.dta"
  save "allyears.dta"
}

By "combine into a panel," I'm thinking you mean "append." You will presumably want to use -xtset- if you're planning some kind of panel-oriented analysis of this data.

[Nick Bas]

Thanks, Mike! Indeed you were right about small problems coming up among the files, but I got this to work after correcting those.