command loop: every iteration moves one number up AND one letter up

[Emil Alnor]

I need to do a lot of regression models (differing in weighting and what control variables are included) with four different independent variables. I also want to export the results to excel. For simplicity, lets just say I am interested in the coefficient and standard error of the coefficient:

putexcel set results, sheet(reg) modify
reg y x1
putexcel a1 = _b[x1]
putexcel a2 = _se[x1]

I then need to put the coefficients and standard error of x2 in coloumn b, x3 goes in coloumn c and x4 goes in coloumn d. Writing:

putexcel set results, sheet(reg) modify
forval k = 1/4{
reg y x`k'
putexcel a1 = _b[x`k']
putexcel a2 = _se[x`k']
}

...does not produce the desired result, because this loop does not change the coloumn, and similarly:

putexcel set results, sheet(reg) modify
foreach k = a b c d{
reg y x1
putexcel `k'1 = _b[x1]
putexcel `k'2 = _se[x1]
}

...just puts the results of x1 in different coloumns. I know I could just put the results of the other x'es in the rows below, but I need to do a lot of different models (e.g. 'reg y x1 z1', 'reg y x1 z2', 'reg y x1 z1 z2' and so on), and for analysis it would be very convenient to have the results of the different x'es in coloumns next to each other.

Is there a way where I can tell my loop to BOTH go from 1 to 2 AND a to b, and so forth every iteration? Or any other solution?

[Maarten Buis]

Here is a possibility. For generality I stored the independent variables in a local, so they don't need to be numbered. I use the Mata function numtobase26() to get the columnletter, so you don't get stuck if you ever get beyond 26 models. I solved your problem by making a separate local that is incremented as the loop runs.

putexcel set results, sheet(reg) modify

local i = 1
local indeps x1 x2 x3 x4
foreach indep of local indeps
    reg y `indep'
    mata: st_local("col", numtobase26(`i'))
    putexcel `col'1 = _b[x`k']
    putexcel `col'2 = _se[x`k']
    local i = `i' + 1
}

[Nick Cox]

I have to be curious. Even in MS Excel is it really easier to work with a wide layout in which another model means yet more columns to put those results?

[Emil Alnor]

Here is a possibility. For generality I stored the independent variables in a local, so they don't need to be numbered. I use the Mata function numtobase26() to get the columnletter, so you don't get stuck if you ever get beyond 26 models. I solved your problem by making a separate local that is incremented as the loop runs.

putexcel set results, sheet(reg) modify

local i = 1
local indeps x1 x2 x3 x4
foreach indep of local indeps
reg y `indep'
mata: st_local("col", numtobase26(`i'))
putexcel `col'1 = _b[x`k']
putexcel `col'2 = _se[x`k']
local i = `i' + 1
}

Thanks, Marteen Buis!

[Emil Alnor]

I have to be curious. Even in MS Excel is it really easier to work with a wide layout in which another model means yet more columns to put those results?

Nick Cox I have results (coef, se) from the regression taking one row each, and then different x'es taking one coloumn each. Here resulting in a a 2x4 table (2 rows 4 coloumns). I could also go with a 4x2 table, which I suppose it what you would refer to as a long layout, but I also want this table but for different models (different control variables), i.e. I want the other tables below. So to minimize the number of rows of the resulting sheet, and since 4 coloumns is still manageable in terms of wideness, I go for the 2x4-table here.