Loop in Stata: How to obtain results in Tables suitable for Word (or Latex) export?

[Michael Duarte Goncalves]

Hello,

I would like to extract this code in a nice table format in Word (or Latex and even Excel):

foreach var of varlist wasgach wasgeink wasfihan wasmfqe ///
               wasfach11 wasfort wasfawe wasmtza wasbeta wasfrei {
*                  
    recode    `var' (-1 -9 = .) //recoding missing variables==. enumerated in "var"
*
*
// loop for Bachelor_hes only
    forvalues i = 0/3    {
    
        local ba_hes Bachelor_hes
        foreach c of local ba_hes    {
            svy,subpop(`c'`i'):mean `var'
        }
    }
*
*
// loop for Bachelor_heu only
    forvalues i=0/2{
    
        local ba_heu Bachelor_heu
        foreach c of local ba_heu    {
            svy,subpop(`c'`i'):mean `var'
        }
    }
}

However, I only get "individual" rows on Stata. How can I get a table for each of the above variables, so that it can be extracted?

I'm obviously open to improvements to this code, which surely has many errors. It seems to me that Nick Cox had already pointed out that "local" was not necessary in these cases, but I'm not sure (Please correct me if I have written atrocities, Nick Cox).

Thanks for the help.

[George Ford]

I often stuff such stats in a matrix, or you can putexcel on each round.

The mean command will store r(mean), which is what you keep.

matrix R = J(100,2,.)
forv i = 1/100 {
     summ x if id==`i', meanonly
     matrix R[`i',1] = `i'
     matrix R[`i',2]= `r(mean)'
}

[Michael Duarte Goncalves]

Thanks George Ford

[George Ford]

you can svmat the matrix to variables and then export excel those variables if you need it in excel.
or, you can do things with those new variables as you would any other, but keep in mind the new variables are not "aligned" with the data and may be greater than or less than your original sample size. That's why I kept `i' in the first column, so you can identify them.

You might look at asreg. You can avoid the loop by rolling on the variable of interest and it will store the coefficients (which would be a constant to get the mean). Not sure it works with svy, however.

[Michael Duarte Goncalves]

George Ford : I have just three more questions, please?

1. Do you put

matrix R = J(100,2,.)
forv i = 1/100 {    
       summ x if id==`i', meanonly      
       matrix R[`i',1] = `i'    
       matrix R[`i',2]= `r(mean)'
}

At the end of the loop or in-between?

2. It's probably a silly question, but where I am supposed to find the putexcel file?

3. What do you mean by "putexcel on each round" ?

Thanks in advance.

[George Ford]

after you populate the matrix.

matrix R = J(100,2,.)
forv i = 1/100 {
summ x if id==`i', meanonly
matrix R[`i',1] = `i'
matrix R[`i',2]= `r(mean)'
}
matrix list R // if you want to see it
capture drop R*
svmat R
summ R2
export excel R1 R2 using resultsexport.xlsx

you can name the rows and tell svmat to use those names, thus replacing R1 R2 with your chosen names.

[Michael Duarte Goncalves]

Thanks George Ford

[Michael Duarte Goncalves]

Hi,

As I was struggling with Code cited in #6, I tried the following one, which you suggested at #2 also. But it appears I coded it wrong:

putexcel set putexcel2.xlsx, sheet(example1) replace
putexcel A1 = "Variables"
putexcel B1 = "Means"
local row = 2
foreach var of varlist wasgach wasgeink wasfihan wasmfqe ///
               wasfach11 wasfort wasfawe wasmtza wasbeta wasfrei {
*                  
    recode    `var' (-1 -9 = .) //recoding missing variables==. enumerated in "var"
*
*
// loop for Bachelor_hes only
    forvalues i = 0/3    {
    
        local ba_hes Bachelor_hes
        foreach c of local ba_hes    {
        svy,subpop(`c'`i'):mean `var'
        putexcel A`row' = `c'`i'
        putexcel B`row' = `r(mean)'
        local ++row
        }
    }

*
// loop for Bachelor_heu only
    forvalues i=0/2{
    
        local ba_heu Bachelor_heu
        foreach c of local ba_heu    {
            svy,subpop(`c'`i'):mean `var'
        }
    }

}
*

The error is the following:

r(198);

Could anyone help me please?

Thanks.

[Nick Cox]

Where does the code fail?

[Michael Duarte Goncalves]

Since I added the following 3 lines:

        putexcel A`row' = `c'`i'
        putexcel B`row' = `r(mean)'
        local ++row

The error shown by Stata is :

nothing found where expression expected
r(198);

[George Ford]

You are looping the variables over values of Bachelor_hes, but what is the "forv i" loop doing?

[Michael Duarte Goncalves]

Basically, Bachelor_hes`i', for `i'=0,1,2,3 are four variables equal to one if `i' = i and 0 if `i'=`j'

[George Ford]

Apparently, the mean command is giving you r(mean). You'll need to pull it out of r(table).

local m = el(r(table),1,1).
putexcel B`row' = `m'

might want to post some data with dataex

[Michael Duarte Goncalves]

Yes of course:

	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input byte(Bachelor_hes0 Bachelor_hes1 Bachelor_hes2 Bachelor_hes3) float(wasgach wasgeink wasfihan wasmfqe wasfach11 wasfort wasfawe wasmtza wasbeta wasfrei)
                    0 0 0 1  3.643208 3.3363106  2.848194 4.2207265  4.137245 3.4350355  4.318081 3.142821  3.558937  3.447327
                    0 0 1 0 3.8655815  3.197785  2.490923 3.8986254  4.252775   3.26073 4.2351255  2.82523 3.4657536 3.3945854
                    0 1 0 0 3.9821274  3.299829  2.629338  3.918392 4.1415873  3.406997 4.2691464 2.583238  3.292967 3.3158536
                    1 0 0 0  3.117204   3.21496 2.3304517 4.1810036   4.32603  3.246392  4.099856 3.467509  3.849992  3.435438
                     . . . .   3.94062 3.4107394 2.7856965  4.080115  4.012834  3.560109  4.305047 3.075464  3.833161  3.625349
                  end

[George Ford]

what's the svy set line?