Changing two variables in a foreach loop in order to have putexcel outputs go to different location in Excel

[Jay Gold]

Hi,

I am looking to do repeated regression correlations. In this case, the variable "age" is being compared to a few variables: "Cortisol", "Endothelin1", "FGF21". There are many more, but I am just showing 3 for simplicity sake. I like the output of the regression results in the matrix table format. I am having trouble getting it into Excel. I would like to output each r(table) of the regression results to Excel, in column B but with each one with a few spaces. I am trying to a break of 15 rows.

local row = 2

foreach var of varlist Cortisol Endothelin1 FGF21 {
    
    regress age `var'
    matrix list r(table)
    putexcel set results, modify
    putexcel B`row' = matrix(r(table)), colnames
    global `row' = `row'+15
    di `row'
    
        }
*

The line

 di `row'

is there for now as a check to me that "row" is being changed. It is NOT needed, really.

The line below does not work to add value to "row" for each loop.

 global `row' = `row'+15

With out that line, the code runs. However, it just drops each table in the same place (staring in cell B2) and replacing the prior table.

I need to effectively add value to the "row" variable. Also, it would be nice to add rownames as well. But for some reason the putexcel command with a matrix only allows for colnames OR rownames. A simple way to do that would also be greatly appreciated! Thanks for any tips!!

[Daniel Schaefer]

This isn't the correct use of the global keyword. Try this:

local row = 2

foreach var of varlist Cortisol Endothelin1 FGF21 {
    regress age `var'
    matrix list r(table)
    putexcel set results, modify
    putexcel B`row' = matrix(r(table)), colnames
    local row = `row'+15
    di `row'
}

[Jay Gold]

This isn't the correct use of the global keyword. Try this:

local row = 2

foreach var of varlist Cortisol Endothelin1 FGF21 {
regress age `var'
matrix list r(table)
putexcel set results, modify
putexcel B`row' = matrix(r(table)), colnames
local row = `row'+15
di `row'
}

This does not work either. The variable "row" is not increased. I added in the code di `row' to check if "row" is being increased and it is not.

[Daniel Schaefer]

It works for me:

local row = 2
foreach var in Cortisol Endothelin1 FGF21 {
    local row = `row'+15
    di `row'
}

. local row = 2

. foreach var in Cortisol Endothelin1 FGF21 {
  2.     local row = `row'+15
  3.     di `row'
  4. }
17
32
47

. 
end of do-file

The console prints 17, 32, 47 as expected.

[Jay Gold]

This worked perfectly. Thanks Daniel!

[Daniel Schaefer]

Great, glad to hear it Jay. Welcome to the Stata forums.