Hi,
I am trying to create a panel data.
The raw data is multiple excel files (ranging from one to three) for each year, and I am using the following loop commands.
(Please note that I have manually renamed excel files to "year_filenumber" where filename=1,2,3 representing the number of excel files in the year. Thus, the loop works)
The problem is that years which have data in only one or two excel files are imported extra times.
For example, 2005_1 is the only excel file for year 2015. When it gets imported in Stata, I get three .dta files (2015_1, 2015_2, 2015_3) which get appended into one 2015.dta file (leading to duplicates).
Is there any way to correct this loop (instead of using "duplicates drop" command in the end) ? Thank you.
I am trying to create a panel data.
The raw data is multiple excel files (ranging from one to three) for each year, and I am using the following loop commands.
(Please note that I have manually renamed excel files to "year_filenumber" where filename=1,2,3 representing the number of excel files in the year. Thus, the loop works)
Code:
*import .xls files into .dta foreach year in 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015{ forvalues i=1/3 { capture import excel "`year'_`i'", sheet("`year'_`i'") firstrow case(lower) allstring clear capture saveold "`year'_`i'", replace } } *create yearly .dta files clear all foreach year in 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015{ forvalues i=2/3 { use "`year'_1", clear capture append using "`year'_`i'" } generate time="`year'" saveold "`year'", replace } *append yearly .dta files clear all use 2001 foreach year in 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015{ append using `year' }
For example, 2005_1 is the only excel file for year 2015. When it gets imported in Stata, I get three .dta files (2015_1, 2015_2, 2015_3) which get appended into one 2015.dta file (leading to duplicates).
Is there any way to correct this loop (instead of using "duplicates drop" command in the end) ? Thank you.
Comment