Program a Putexcel macro to extract estimates and move to the next Excel Row for the next output?

[Nate Fitzpatrick]

Hello all,

I am running a number of univariate logistic regressions, so far I have been copying all my hand and this has been very tedious so I was hoping to write a macro to store the estimates of the regression, output them to an excel spreadsheet, and then shift down 1 row for the next output. I like having control over the type of regression and the variables in the regression, so I don't need that in the program.

My current code:

*make sure the previous attmept is cleared
capture program drop mymatrix

*Set the starting row number.
local rownum = 2

*Set up the program
program define mymatrix
matrix table = r(table)
matrix list table
matrix b = table [1, 1...]'
matrix ll = table [5, 1...]'
matrix ul = table [6, 1...]'
matrix pvalue = table [4, 1...]'

putexcel A`rownum' = matrix(b), nformat(0.00)
putexcel C`rownum' = matrix(ll), nformat(0.00)
putexcel D`rownum' = matrix(ul), nformat(0.00)
putexcel ​​​​​​​E`rownum' = matrix(ul), nformat(0.00)

local rownum = `rownum' + 1
end

*Regressions
stcox sex
mymatrix

ERROR:
A: invalid cell name

The first part of the code works great if I input the "putexcel" targets directly, but I was hoping to expedite this.

Thank you!

[Andrew Musau]

This should be useful in helping you understand the behavior of local macros: https://journals.sagepub.com/doi/10....36867X20931028.

[Nate Fitzpatrick]

This should be useful in helping you understand the behavior of local macros: https://journals.sagepub.com/doi/10....36867X20931028.

Ah that's perfect, I didn't realize that 'local' macro's were so limited when working within a Do-File.

Replace everything with a global macro and it (*seems to be*) working great for now!

Thank you!

[Leonardo Guizzetti]

Ah that's perfect, I didn't realize that 'local' macro's were so limited when working within a Do-File.

Replace everything with a global macro and it (*seems to be*) working great for now!

Thank you!

I think that’s rather the wrong lesson to take from this. Locals will work just fine when running the entire do file at once, just not when interactively running commands as you build your program.