Using loop in a program to export excel files

[Greg Saldutte]

Hello,

I have the following program:

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

Ideally, this would export a distinct spreadsheet for 1900, 1910, 1920, 1930, and 1940. Instead, it exports five spreadsheets (named ttest_1900, ttest_1910, etc.) five times, which all contain the same content. This occurs five times, with the existing spreadsheets being replaced each time through the loop. End the end, I am left with five spreadsheets (named ttest_1900, ttest_1910, etc.), all containing 1940 dated. Any advice on how to fix this is appreciated.

[William Lisowski]

I'm a bit confused. I don't see anything in the forval loop that restricts the output data to a single year. I would have expected - assuming you have a variable named year in your dataset - something like

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

[Greg Saldutte]

Thank you for your response. You're right that nothing in the loop restricts the data output to a single year. I need to change that.

I did try the above code, but upon running the do file, Stata gives me the error "year not found," which is odd considering I do have a variable named year.

[Clyde Schechter]

I would also remark that -sheet(data_`1')- is a problem because you have not defined a local macro 1.

[William Lisowski]

Clyde - I thought the same, but this code is within a program, so the local macro `1' is the first argument to the program.

Greg - From the code you show above,

keep mu_* sd_* N_* se_* p_*

is where you lost the year variable you had on the way into the program.

[Clyde Schechter]

Ah yes, I had overlooked that it's inside a program.

[Greg Saldutte]

William - Thanks. I've now kept the year variable, and included if year==`year' after the export command.

Interestingly, when I do this, end up with the error that observations must be between 1 and 1048576, but also with a spreadsheet that contains sheets for each of the target years.