Error using loop with export excel command

[Greg Saldutte]

Hello,

I'm running into a problem when I attempt to use the export excel command.

I have defined a porgram called saveTtest, which ends by exporting to excel:

export excel using "$saveDir/ttest_"`year'".xlsx", ///
sheet(data_`1') firstrow(var) sheetreplace

I am running t-tests over a loop of of five years. at the end of the loop, I call the program:

saveTtest "ttest_"`year'".xlsx"

This returns the error, invalid '".xlsx'. I've tried playing around with the name of the file following export excel, and anytime i don't place quotes around `year' as if it were a loop, Stata simply exports a file called ttest_.xlsx. Any input as to what I'm doing wrong with this file name is appreciated.

[Jesse Wursten]

This is very hard to debug without seeing the code of your program, but there should not be double quotes around the `year'. I'm surprised the program even runs with them there to be honest. If it saves the file as ttest_.xlsx, that means your local "year" is not defined within the scope of your program. Did you define year in the program, or in a loop outside the program? If the latter, you need to pass it to saveTtest as an option (see "help syntax"). Alternatively, you can take the easy but dirty way out by using globals, e.g. global year = "`year'" and then referring to ${year} in your program.

Another thing that you might be itnerested in: anything you place after saveTtest is stored in `0' as far as the program is concerned. So if you want to refer to the filename in your program, you can simply place `0' instead of writing it out entirely. If you only need the year-local to define your filename, then you don't even need to bother with what I said above, as you'll pass on the full name in one go.

[Greg Saldutte]

Thank you for your response. You are right, I did not define the year in the program. This is the code that I had:

program define saveTtest
preserve
keep mu_* sd_* N_* se_* p_*
keep if _n==1
gen id=1
order id, first
reshape long mu_1_ mu_2_ ///
sd_1_ sd_2_ N_1_ N_2_ se_ p_, ///
i(id) j(varnames) string
order varnames, first
rename mu_1_ mu_1
rename mu_2_ mu_2
rename sd_1_ sd_1
rename sd_2_ sd_2
rename N_1_ N_1
rename N_2_ N_2
rename se_ se
rename p_ p
drop id
export excel using "$saveDir/ttest_"`year'".xlsx", ///
sheet(data_`1') firstrow(var) sheetreplace
restore

[Greg Saldutte]

I've not changed my program to the following:

program define saveTtest
preserve
keep mu_* sd_* N_* se_* p_*
keep if _n==1
gen id=1
order id, first
reshape long mu_1_ mu_2_ ///
sd_1_ sd_2_ N_1_ N_2_ se_ p_, ///
i(id) j(varnames) string
order varnames, first
rename mu_1_ mu_1
rename mu_2_ mu_2
rename sd_1_ sd_1
rename sd_2_ sd_2
rename N_1_ N_1
rename N_2_ N_2
rename se_ se
rename p_ p
drop id
forval year=1900(10)1940 {
export excel using "$saveDir/ttest_`year'.xlsx", ///
sheet(data_`1') firstrow(var) sheetreplace
}
restore
end

This does export the desires spreadsheet for 1990. However, for 1910, I get the error that ttest_1910 is too big.

[Jesse Wursten]

Does it provide more details than that? I don't know what the export excel limitations are. Also, what size of data are we talking about?

[Greg Saldutte]

Thank you for your continued responses. There must have been a problem with the limitations on my computer. I have changed to another computer, and Stata has no problem exporting the spreadsheets.

Now I am running into the problem that instead of exporting the appropriate t-tests for every 10 years between 1900 and 1940, Stata exports ttest_1900, ttest_1910, etc. five times:

file /Users/gsaldutte/Downloads/ttest_1900.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1910.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1920.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1930.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1940.xlsx saved
(0 observations deleted)
(668 observations deleted)
(1,903 observations deleted)
(313,510 observations deleted)
(62,282 observations deleted)
(41,498 observations deleted)

That's what Stata shows the first time around. The fifth time, it shows:

file /Users/gsaldutte/Downloads/ttest_1900.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1910.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1920.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1930.xlsx saved
file /Users/gsaldutte/Downloads/ttest_1940.xlsx saved

And I'm left with five files all with the same content, i.e., Stata performs the t-tests for 1900 and saves them to 5 separate files with the same content (named ttest_1900, ttest_1910,...,ttest_1940), then does the same thing and rewrites these files for the years 1900(10)1940. Any input as to why my program produces this is appreciated.

[Jesse Wursten]

Well, currently that's exactly what you ask Stata to do, no?

 
 forval year=1900(10)1940 { export excel using "$saveDir/ttest_`year'.xlsx", /// sheet(data_`1') firstrow(var) sheetreplace }

This simply says: first, export current data to $savedir using as filename ttest_1900. Then do the same using filename ttest_1910. If you want Stata to do something specific for specific years, you have to put that into the loop.