Combining multiple lines of code to "putexcel" to avoid using a new command line for every "putexcel" command

[Jay Gold]

Hi there,

I am running a loop with multiple ttest comparisons. I want to output the means, SE's, and p-values to Excel. I have figured how to do this successfully. However, I am using a separate line of the "putexcel" command. I realize this opens and closes Excel and makes for a very slow do file. Is there a way to combine multiple actions with putexcel to avoid opening and closing it 8x for something relatively simple?

    local row = `row'+1
    putexcel B`row' = "Decrease group Avg=", right
    local m1 = r(mu_1)
    putexcel C`row'= `m1'
    local row = `row'+1
    putexcel B`row' = "SE =", right
    local se1 = (r(sd_1))/sqrt(r(N_1))
    putexcel C`row'= `se1'
    local row = `row'+1
    putexcel B`row' = "Increase group Avg=", right
    local m2 = r(mu_2)
    putexcel C`row'= `m2'
    local row = `row'+1
    putexcel B`row' = "SE =", right
    local se2 = (r(sd_2))/sqrt(r(N_2))
    putexcel C`row'= `se2'
    local row = `row'+2

[Leonardo Guizzetti]

The approach is to use the -open- option when opening your Excel file which tells Stata to open the file in memory, making changes there, and then saving out the Excel file when done. This way it's only one open/close operation and will greatly speed things up.

[Jay Gold]

The approach is to use the -open- option when opening your Excel file which tells Stata to open the file in memory, making changes there, and then saving out the Excel file when done. This way it's only one open/close operation and will greatly speed things up.

Hmm, I do not see that command. Can you give me an example?

[Leonardo Guizzetti]

Sure, here's a template. Note, -open- is an option to -putexcel set-, not a command.

putexcel set .... , open ...
.... your code ...
putexcel save

[Jay Gold]

Hmm, that did not quite work.

This is the original code:

local row = 2
putexcel set results_dd.xlsx, sheet (test) modify
summarize HSCRP if d_HGBL == 1, detail
local q50 = r(p50)
putexcel B`row' = "Increase group Median =", right
putexcel C`row'= `q50'
local row = `row'+1
local q25 = r(p25)
putexcel B`row' = "Q25% =", right
putexcel C`row'= `q25'
local row = `row'+1
local q75 = r(p75)
putexcel B`row' = "Q75% =", right
putexcel C`row'= `q75'

local row = `row'+3
summarize HSCRP if d_HGBL == -1, detail
local q50 = r(p50)
putexcel B`row' = "Decrease group Median =", right
putexcel C`row'= `q50'
local row = `row'+1
local q25 = r(p25)
putexcel B`row' = "Q25% =", right
putexcel C`row'= `q25'
local row = `row'+1
local q75 = r(p75)
putexcel B`row' = "Q75% =", right
putexcel C`row'= `q75'

This is how I changed it to use -open-

local row = 2

quietly summarize HSCRP if d_HGBL == 1, detail
local q50 = r(p50)
putexcel set results_dd.xlsx, open sheet (test) modify
 B`row' = "Increase group Median =", right
 C`row'= `q50'
local row = `row'+1
local q25 = r(p25)
 B`row' = "Q25% =", right
 C`row'= `q25'
local row = `row'+1
local q75 = r(p75)
 B`row' = "Q75% =", right
 C`row'= `q75'

local row = `row'+3
summarize HSCRP if d_HGBL == -1, detail
local q50 = r(p50)
 B`row' = "Decrease group Median =", right
 C`row'= `q50'
local row = `row'+1
local q25 = r(p25)
 B`row' = "Q25% =", right
 C`row'= `q25'
local row = `row'+1
local q75 = r(p75)
 B`row' = "Q75% =", right
 C`row'= `q75'
 putexcel save

This is the output

. *
. local row = 2

.
. quietly summarize HSCRP if d_HGBL == 1, detail

. local q50 = r(p50)

. putexcel set results_dd.xlsx, open sheet (test) modify
note: file will be modified when putexcel save command is issued.

.  B`row' = "Increase group Median =", right
command B2 is unrecognized
r(199);

end of do-file

r(199);

.

[Leonardo Guizzetti]

I did not suggest that you remove -putexcel- commands that you already had in place. You need to put those back in. Telling Stata to open the Excel file in memory means that it is not reading and writing the Excel file to disk with every issuance of a putexcel command. Instead, the file is modified in memory, and then only saved to the disk once you call -putexcel save-. This speeds up the overall creation of an Excel file, but you still have to issue putexcel commands in the usual way so Stata knows what to do.

See examples in -help putexcel-.

[Jay Gold]

I figured it out. Posting my code here so others who are new to Stata have a concrete example to go by.

putexcel clear
local row = 2
putexcel set results_dd.xlsx, sheet (test) open modify
summarize HSCRP if d_HGBL == 1, detail
local q50 = r(p50)
putexcel B`row' = "Increase group Median =", right
putexcel C`row'= `q50'
local row = `row'+1
local q25 = r(p25)
putexcel B`row' = "Q25% =", right
putexcel C`row'= `q25'
local row = `row'+1
local q75 = r(p75)
putexcel B`row' = "Q75% =", right
putexcel C`row'= `q75'

local row = `row'+3
summarize HSCRP if d_HGBL == -1, detail
local q50 = r(p50)
putexcel B`row' = "Decrease group Median =", right
putexcel C`row'= `q50'
local row = `row'+1
local q25 = r(p25)
putexcel B`row' = "Q25% =", right
putexcel C`row'= `q25'
local row = `row'+1
local q75 = r(p75)
putexcel B`row' = "Q75% =", right
putexcel C`row'= `q75'
putexcel save