Putexcel with logistic regression using a loop

[Jasper Tromp]

Dear All,

I am trying to loop a logistic regression for a number of variables that of which I want results to be exported to Excel, so far unfortunately it is not working. I am hoping for some feedback with regard tot the syntax I've written so far.

foreach X of varlist A B C {
logistic SEX `X'
putexcel A`row'=("`X'") B1`row'=(r(Odds ratio)) using bla.xlsx, modify keepcellformat sheet("results")

}

I have tried a number of variances of the syntax above. I am using stata 13 and for some reason it is not accepting the `row' which I have seen used in a number of different syntaxes.

the `row' gives me:

A: invalid cell name
r(198);

What I am basically trying to get is a table which in the A would give me the names of the variables A; B; C etc., B the odds ratio, C the p-value and D(and or E) the 95% CI.

Thanks in advance.


Best,

Jasper Tromp

[Nick Cox]

`row' would be a reference to a local macro, but you never define that macro so far as we can see. Referring to a local macro that doesn't exist is not itself an error but its being interpreted as an empty string could be an error for other reasons, as here. You may want something more like

  
local row = 1  
foreach X of varlist A B C {    
     logistic SEX `X'    
     putexcel A`row'=("`X'") B1`row'=(r(Odds ratio)) using bla.xlsx, modify keepcellformat sheet("results")    
     local row = `row' + 1  
}

When referring to code elsewhere, explicit references often help mightily.

I suspect that B1 above should just be B but I don't really understand spreadsheets.

[Jasper Tromp]

Thank you very much for your reply. I have found another fault in my code, since p-values are stored in e, not r. Unfortunately I have found out that it does not store the odds ratio nor the 95% confidence interval. Are there any solutions to save the odds ratio and 95% CI in e or r? By the way mr Cox, you were right with regard to B, it becomes B instead of B1.

The adjusted code looks like this, where p is the associated p value:

 local row = 1  
 foreach X of varlist A B C {          
                       logistic SEX `X'          
                       putexcel A`row'=("`X'") B`row'=(e(p)) using bla.xlsx, modify keepcellformat sheet("results")          
                       local row = `row' + 1  
}

[Nick Cox]

I didn't check your use of returned results. What is accessible is given in

 
ereturn list

Also check for various Stata Journal Tips by Maarten Buis.

[Jasper Tromp]

Dear All,

Unfortunately stata does not store either the OR or the 95%CI in e, only the p-value.

Would their be any way to force stata to store said values (OR/95%CI)?

[Rich Goldstein]

yes, these are stored in r(table):

sysuse auto
logistic fore price mpg
mat li r(table)