Help with loop to save output from multiple vars

[Anne Dam]

Dear stataforum

I am trying to save effect sizes from multiple variables into excel or csv, and I am struggling to make my code save the data I am interested in.

I have tried to make example data that somehow resembles my own data set, except my own data set contains many more "TotalScore"s (and they are not random numbers)

Imaging I want to save Hedge'g and 95CI for all TotalScore_* in excel, with one row corresponding to each TotalScore_

use https://www.stata-press.com/data/r17/depression ,clear

/*
  Remake example data to resemble that a total of 5 outcome vars were measured on participants. 
  Here they are random numbers, but lets pretend they are real measured outcome vars
  and we want to know how "sex" may affect each TotalScore_*.
*/

ren TotalScore TotalScore_5
gen TotalScore_6 = runiform(1,5)
gen TotalScore_7 = runiform(100,200)
gen TotalScore_8 = runiform(10,20)

*Start preparing excel
putexcel set "$EXCELDIR/TestTable", sheet("ES with CI") modify   //perhaps insert your own local excel path here
putexcel A1=("") B1=("Hedge's g") C1=("low 95CI") D1=("high 95CI") 

*Looping starts
foreach var of varlist TotalScore_5-TotalScore_8 {
    di "`var'" 
    esize twosample `var' , by(sex)
       scalar `var'_g= r(g)
       scalar `var'_l= r(lb_g) 
       scalar `var'_u= r(ub_g) 

foreach x in 1/5 {
    local row = `x'+1
putexcel A`row'=("`var'") B`row'=(`var'_g) C`row'=(`var'_l) D`row'=(`var'_u)
}
}

With this code I only get 1 line in my excel, which is the last statistics (TotalScore8).

I would greatly appreciate any help and suggestions

I am using Stata 17 for PC

[Nick Cox]

You need

forval x = 1/5

or to use foreach with of not in. The two syntaxes of foreach can’t be combined. As it happens 1/5 is here allowed as a one item list, but it can only be interpreted in your code as a fraction equivalent to 0.2.

[Anne Dam]

Aha! Thanks Nick!
Now at least I get 5 rows in excel, but they all contain the last statstics; "TotalScore_8", and not one row per TotalScore ?

*Looping starts
foreach var of varlist TotalScore_5-TotalScore_8 {

    di "`var'" 
    esize twosample `var' , by(sex)
       scalar `var'_g= r(g)
       scalar `var'_l= r(lb_g) 
       scalar `var'_u= r(ub_g) 

    forval x = 1/5 {
    local row = `x'+1
putexcel A`row'=("`var'") B`row'=(`var'_g) C`row'=(`var'_l) D`row'=(`var'_u)
}
}

[Nick Cox]

If you look at your loops again I think you will see that you overwrite each variable’s results with the next variable’s as you loop over the same rows, 1 to 5.

[Anne Dam]

Thank you so much Nick, you're right! Thanks for taking the time to help me identify the problem!

I finally made it work with:

use https://www.stata-press.com/data/r17/depression ,clear

/*
  Remake example data to resemble that a total of 5 outcome vars were 
  measured on participants. 
  Here they are random numbers, but lets pretend they are real measured 
  outcome vars and we want to know how "sex" may affect each TotalScore_*.
*/

ren TotalScore TotalScore_5
gen TotalScore_6 = runiform(1,5)
gen TotalScore_7 = runiform(100,200)
gen TotalScore_8 = runiform(10,20)

*Start preparing excel
putexcel set "$EXCELDIR/TestTable", sheet("ES with CI") modify   // <--Insert own path
putexcel A1=("") B1=("Hedge's g") C1=("low 95CI") D1=("high 95CI") 

*Looping starts
local row=2
foreach var of varlist TotalScore_5-TotalScore_8 {
        di "`var'" 
        esize twosample `var' , by(sex)
        scalar `var'_g= r(g)
        scalar `var'_l= r(lb_g) 
        scalar `var'_u= r(ub_g) 
        putexcel A`row'=("`var'") B`row'=(`var'_g) C`row'=(`var'_l) D`row'=(`var'_u)
        local ++row
}