foreach command and saving regression results without overwriting

[Betty Wolde]

Hi,

I am trying to add a different column with the regression results for each of the variables in the varlist and have the last regression rewriting at the end. Would appreciate any leads on how I can fix this.

foreach var of varlist cd1 cd2 cd3 cd4 cd5 {

quietly: regress lv i.`var'_quintile
estimates store model1
quietly: regress lv_mass age i.racecat i.cohort i.educabas_cat i.`var'_quintile
estimates store model2
quietly: regress lv age i.racecat i.cohort i.educabas_cat i.`var'_quintile
estimates store model3
local all_models `all_models' model1 model2 model3
etable, estimates(model1 model2 CVD) mstat(N) mstat(r2_a) column(index) ///
showstars showstarsnote cstat(_r_b) title("Hep C association") cstat(_r_ci, nformat(%6.1f) cidelimiter(",")) ///
export("/Users/test.xlsx", as(xlsx) sheet(Sheet2) cell(A1) append)
}

[Nick Cox]

Some questions to ease the way for people who might answer this (not me; I am not a big user of this kind of analysis, but many others are, or need to do it):

1. You store model3 but don't try to export it.
2, Other way round with CVD.

Please clarify.

There's probably a simple story here, perhaps just this: your real code is much more complicated or has arcane names and you're trying to simplify it for us, but your rewriting wasn't complete or consistent.

[Clyde Schechter]

Well, the -etable- command's -export()- option, which creates the output, contains absolutely nothing in it that changes from one iteration of the loop to the next. (Otherwise put, it makes no mention of `var'.) So it is writing the output from each iteration into the same workbook on the same tab at the same cell.

If you want to get the output for each of the variables cd1 through cd5 on a separate tab in the workbook, you need to change the -sheet()- suboption to -sheet(`var')-, or provide some other name that incorporates `var' so that each iteration of the loop will put the output on a separate sheet. If you are hoping to get all five iterations' output on a single tab in the workbook, but starting at different places, then that is more complicated because you will have to somehow calculate the correct starting cell for each iteration--which requires knowing how many Excel rows each iteration's output will take. If you want a separate workbook for each iteration's results, you need to somehow incorporate `var' into the name of the workbook in the -export()- option.

Note that -append- is not a valid option for exporting to Excel.

Let me also point out some other problems/oddities in your code that don't directly bear on the question you are raising:

1. Your third regression results get stored as model3. But you never do anything with model3.
2. You create a local macro, all_models, but you never use it for anything.
3. The -estimates()- option in -etable- refers to a model CVD that, at least within the code you show, does not exist. I suppose you created that one earlier in the code than where you begin in your post. But the CVD estimates do not, therefore, change with each iteration of the loop. So, once you get the output straightened out, the exact same CVD results will be copied five times, in five different places in your output. It's OK to do that if that's what you want, but seldom what people intend to do. So I'm raising it as a possible mistake in your code.

Added: Crossed with #2.

[Betty Wolde]

Thank you Clyde and Nick. Correct, it was a very long code and trying to simplify it here to present the idea of the issue I was having.
Clyde, thank you! That answers my question, I was trying to place the regression results on the same sheet but in different columns sequentially for each of the variables in varlist. I guess I will have to do one sheet for each variable and collate them manually. I appreciate the additional feedback.

[Clyde Schechter]

I guess I will have to do one sheet for each variable and collate them manually. I appreciate the additional feedback.

No, it does not have to be done that way. And collating manually is both tedious and error-prone. So here's how you can code this:

local result_width 15
local column_names `c(ALPHA)'
forvalues j = 1/26 {
    local add_on `c(ALPHA)'
    local add_on: subinstr local add_on " " " `:word `j' of `c(ALPHA)''", all
    local column_names `column_names' `:word `j' of `c(ALPHA)''`add_on'
}

local j 1
foreach var of varlist cd1 cd2 cd3 cd4 cd5 {
    local cell_num = (`j'-1)*`result_width' + 1
    local cell `:word `cell_num' of `column_names''
    quietly: regress lv i.`var'_quintile
    estimates store model1
    quietly: regress lv_mass age i.racecat i.cohort i.educabas_cat i.`var'_quintile
    estimates store model2
    quietly: regress lv age i.racecat i.cohort i.educabas_cat i.`var'_quintile
    estimates store model3
    local all_models `all_models' model1 model2 model3
    etable, estimates(model1 model2 CVD) mstat(N) mstat(r2_a) column(index) ///
    showstars showstarsnote cstat(_r_b) title("Hep C association") cstat(_r_ci, nformat(%6.1f) cidelimiter(",")) ///
    export("/Users/test.xlsx", as(xlsx) sheet(Sheet2) cell(`cell') append)
    local ++j
}

The main complication is that in Excel columns are identified by letters A ... Z AA ... AZ BA ... BZ .... So you have to calculate what the column head is if you want to put each iteration's results side by side, to the right, of what preceded it. This can be done emulating base 26 arithmetic, although it is a bit tricky because A please the role of 0 in the first A-Z series, but the role of 1 when it appears as the first of two letters. So I chose the lazy way: just build out the series of column names all the way to ZZ, and then calculate the column placement as a decimal number and select the corresponding letters from the long series of column names.

Now, calculating the column number requires knowing the width of the output. In the code above, I set local macro result_width to 15 on the theory that the output itself would be 10 columns and you might want to skip 15 columns between results. So you should figure out what your actual width is and how many columns you want to skip between results: add those two and set local macro result_width to that value. Since the column name series in this code extends only as far as ZZ, that limits you to a maximum of 702 columns for all of the output and space-between columns. I doubt that limit will bite: a spreadsheet that wide would be so unwieldy as to be unusable for viewing by humans anyway. And if you are trying to generate these outputs as inputs for further computing, rather than for human perusal, Excel is the wrong tool for that job anyway.

[Hemanshu Kumar]

The main complication is that in Excel columns are identified by letters A ... Z AA ... AZ BA ... BZ .... So you have to calculate what the column head is if you want to put each iteration's results side by side, to the right, of what preceded it.

There is a community-contributed command called excelcol, by Sergiy Radyakin and available from SSC, that converts columns numbers to letters for you.

[Clyde Schechter]

Thank you, Hemanshu Kumar , for posting that. I was completely unaware of its existence. While I can't say I have to do this sort of thing often, when I do, I've always regarded it as a supreme nuisance. It's nice to know Sergiy Radyakin did this.

[Betty Wolde]

Thank you, Clyde and Hemanshu! Yes, very glad to hear there is a workaround for this and will give it a try.