Exporting output tables to Word or Excel

[May Blake]

Hello,

I am writing an command to conduct univariate logistic regression for some 1000+ variables using the below code.


foreach var of varlist V1-V1000{
logistic outcome `var'
}


This command gives me what I want, it regresses the outcome with each variable V1, V2, V3 etc separately. Now, I would like to export cleaner versions of the output to an excel sheet or word document. I write the following code for the word document;

putdocx begin
putdocx paragraph
putdocx text ("Binary Outcome")

foreach var of varlist V1 - V1000{
logistic outcome `var'
}
putdocx table TABLE1=etable
putdocx save Example1

The command above does not give me what I want, I only get the last tables for variable V1000.


I also tried the excel output with the following code, I would like to collect the following, Variable, odds ratio, 95% CI and p-value, I would like it to look like this;

Variable OR 95% CI. P
A 1.2. 1.1 - 1.5. 0.002
B 0.99 0.97 - 1.1. 0.23
C 1.5 1.3 - 2.2 0.001
D 0.12 0.09 - 0.11 0.03


I typed the following;
/*WRITE TO columns*/
putexcel A1 = ("Variable") using data..xlsx, replace
matrix b = e(b)’
putexcel A2 = matrix(b), rownames nformat(number_d2)
putexcel B1 = "OR"
putexcel C1 = "95% Conf. Interval"
putexcel D1 = "P>|z|"
putexcel save

But I am getting some of the variable names in rows instead of columns and my data isn't showing up. Thank you in advance for your help.

[Andrew Musau]

Would be easier to extract the output from -r(table)- into a matrix and then export the matrix. Note that logistic has been superseded by the -or- option of logit.

webuse lbw, clear
clear matrix
foreach var in age lwt race{
    logit low `var', or
    mat wanted= nullmat(wanted),r(table)[1..6, 1]
}
mat wanted= wanted'
mat l wanted

Res.:

. mat l wanted

wanted[3,6]
                   b          se           z      pvalue          ll          ul
 low:age   .95013335   .02994229  -1.6231928    .1045482   .89322317   1.0106695
 low:lwt   .98606095   .00608254  -2.2756034   .02286976   .97421116   .99805487
low:race   1.3810679   .23671514   1.8836459   .05961289   .98700425   1.9324624

[Leonardo Guizzetti]

Andrew has given you good advice.

With regards to your first example, the problem with the code is as follows. The loop runs over all variables, estimating a regression model with each iteration, then only once the loop finishes does it write out the last estimation results to your Word file.

This alteration should work (though it's untested).

putdocx begin
putdocx paragraph
putdocx text ("Binary Outcome")

foreach var of varlist V1 - V1000{
logistic outcome `var'
putdocx table TABLE1=etable
}
putdocx save Example1

There's a potential problem though, and that is what you will do with 1000+ regression estimates. In my opinion, it would be better to export each model to separate tabs in an Excel sheet if the entire regression results are important to keep.

[Bjarte Aagnes]

alternative:

clear all

webuse lbw, clear
 
qui foreach var in age lwt race {
    
    collect : logit low `var', or     
}

collect style cell result[ _r_b _r_ci ], nformat(%8.2f)
collect style cell result[ _r_se ], nformat(%4.3f)
collect label drop colname

collect layout ( cmdset colname ) ( result[_r_b _r_se _r_ci] )

collect export test.docx , replace

[May Blake]

Thank you everyone for the great input. All these suggestions worked for me. I used the code suggested by Andrew.