How could I extract a matrix from Stata to put it on Excel or Word?

[Michael Duarte Goncalves]

I have a quick question to ask you. I basically want to extract the following table, by Bachelor (I have four bachelors and the matrix shown below is e(b): 1 x 22):

(running proportion on estimation sample)

Survey: Proportion estimation

Number of strata =      1         Number of obs   =     12,135
Number of PSUs   = 12,135         Population size = 20,798.814
                                  Subpop. no. obs =      4,043
                                  Subpop. size    =  6,826.539
                                  Design df       =     12,134

--------------------------------------------------------------
             |             Linearized            Logit
             | Proportion   std. err.     [95% conf. interval]
-------------+------------------------------------------------
   k_uebverl |
          0  |   .6167363   .0077506      .6014343    .6318104
          1  |   .1194908   .0050217      .1099926    .1296898
          2  |   .0940348   .0044806       .085612    .1031927
          3  |    .020252   .0021745      .0164013    .0249838
          4  |   .0111293      .0017      .0082458    .0150059
          5  |   .0099072    .001611      .0071999    .0136185
          6  |   .0143541   .0019683      .0109656    .0187697
          7  |   .0235214   .0024309      .0191992     .028788
          8  |   .0070225   .0013086      .0048717    .0101133
          9  |   .0067312   .0013486       .004543    .0099628
         10  |   .0066279   .0014243      .0043473     .010093
         11  |    .008272   .0016324      .0056154      .01217
         12  |   .0119176   .0019282       .008674    .0163541
         13  |   .0242573   .0024679      .0198625    .0295952
         14  |   .0115534   .0016617      .0087114    .0153083
         15  |   .0037225   .0009338      .0022758    .0060835
         16  |   .0017873   .0006813      .0008463    .0037706
         17  |    .000448   .0003167       .000112    .0017899
         18  |   .0028701   .0008847      .0015678    .0052485
         19  |   .0048346   .0011266      .0030605    .0076293
         20  |   .0005298   .0003805      .0001296    .0021632
         21  |          0  (no observations)
--------------------------------------------------------------

How can I extract with putexcel (or any other method more suitable for these cases please)?
I tried the following thing, but I have been struggling with Stata for well over an hour now:

putexcel set Partie1_MandatDG2022_mdg.xlsx, sheet(Figure 11) modify
putexcel A2 = "Variables"
putexcel B2 = "Variables Names"
putexcel C2 = "Bachelor"
putexcel D2 = "Variable Labels"
putexcel E2 = "Matrices"
putexcel F2 = "N"
local row = 3
*
*
    forvalues i = 0/3    {
        local ba_hes Bachelor_hes
        foreach c of local ba_hes    {
                svy,subpop(`c'`i'): prop k_uebverl
                local m = matrix(e(b)) // help matrix functions - "el(s,i,j)"
                *You'll need to pull it out of r(table)
                local n = e(N_subpop)
                describe Bachelor_hes`i'
                local varlabel  : var label Bachelor_hes`i'
                local varlabel2 : var label `var'
                putexcel A`row' = "`var'"
                putexcel B`row' = "`varlabel2'"
                putexcel C`row' = "Bachelor_hes`i'"
                putexcel D`row' = ("`varlabel'")
                putexcel E`row' = `m'
                putexcel F`row' = `n'
                local ++row
        }
    }

The error given by Stata is :

type mismatch
r(109);

Thank you so much in advance.

[Jared Greathouse]

Data example with code please!

[Michael Duarte Goncalves]

Yes of course,

* Example generated by -dataex-. For more info, type help dataex
clear
input float k_gewicht byte(Bachelor_hes1 Bachelor_hes2 Bachelor_hes3 Bachelor_hes4 Bachelor_heu1 Bachelor_heu2 Bachelor_heu3) float k_uebverl
1.1702014 0 0 0 1 . . . 1.5714285
1.2235403 0 0 0 1 . . .  1.818182
1.2965906 0 0 0 1 . . .       5.5
1.3012942 0 0 0 1 . . .  2.888889
1.3068033 0 0 0 1 . . .       3.5
1.3681757 0 0 0 1 . . .  2.869565
1.3749917 0 0 0 1 . . .       3.4
1.3932066 0 0 0 1 . . . 4.2727275
1.3941952 0 0 0 1 . . .      5.25
 1.396022 0 0 0 1 . . .       1.2
1.3986723 0 0 0 1 . . .  2.984375
1.3997966 0 0 0 1 . . .         0
1.4092282 0 0 0 1 . . .         4
1.4253764 0 0 0 1 . . .       5.9
1.4266802 0 0 0 1 . . .  2.833333
 1.437978 0 0 0 1 . . .  .3333333
1.4609685 0 0 0 1 . . . 2.3333333
 1.463535 0 0 0 1 . . . 2.3692307
1.4649695 0 0 0 1 . . . 1.5833334
  1.46657 0 0 0 1 . . . 2.1666667
1.4716455 0 0 0 1 . . . 1.6792452
1.4738736 0 0 0 1 . . .  2.351852
1.4783858 0 0 0 1 . . . 2.7419355
 1.487709 0 0 0 1 . . . 3.1636364
1.4894387 0 0 0 1 . . .  .7777778
1.4914602 0 0 0 1 . . .      2.75
 1.496565 0 0 0 1 . . . 3.2258065
1.5058278 0 0 0 1 . . .         6
1.5112525 0 0 0 1 . . .     1.625
 1.517852 0 0 0 1 . . .         1
 1.522194 0 0 0 1 . . .       4.5
1.5247942 0 0 0 1 . . .  .7142857
1.5250627 0 0 0 1 . . . 1.8656716
1.5272105 0 0 0 1 . . . 2.3157895
1.5302317 0 0 0 1 . . .         .
 1.530832 0 0 0 1 . . .       4.8
1.5323747 0 0 0 1 . . . 3.3703704
 1.534475 0 0 0 1 . . .      2.42
1.5399374 0 0 0 1 . . .       2.6
1.5519445 0 0 0 1 . . .  2.620253
 1.555191 0 0 0 1 . . .        15
  1.56484 0 0 0 1 . . . 3.7142856
 1.566451 0 0 0 1 . . . 1.9130435
1.5813878 0 0 0 1 . . . 2.2266667
 1.599385 0 0 0 1 . . . .16666667
 1.605133 0 0 0 1 . . .  2.533333
1.6092395 0 0 0 1 . . .         3
1.6184767 0 0 0 1 . . .  2.958678
 1.618834 0 0 0 1 . . .  3.090909
 1.621372 0 0 0 1 . . .  3.929577
 1.628283 0 0 0 1 . . . 1.3636364
 1.631118 0 0 0 1 . . .       1.5
1.6370845 0 0 0 1 . . . 2.8709676
 1.646706 0 0 0 1 . . .  7.285714
1.6467764 0 0 0 1 . . .  2.372093
1.6499184 0 0 0 1 . . .         3
 1.651609 0 0 0 1 . . .  4.142857
 1.652864 0 0 0 1 . . .         .
1.6539023 0 0 0 1 . . .  3.794643
1.6586413 0 0 0 1 . . . 1.9583334
1.6703442 0 0 0 1 . . .         0
1.6708478 0 0 0 1 . . .         .
 1.670942 0 0 0 1 . . .  2.090909
1.6716428 0 0 0 1 . . .  3.423077
1.6766605 0 0 0 1 . . .  4.230769
1.6805805 0 0 0 1 . . .  3.363636
 1.683109 0 0 0 1 . . .       6.5
1.6867315 0 0 0 1 . . .         0
1.6949947 0 0 0 1 . . . 3.1166666
 1.696562 0 0 0 1 . . .         0
 1.696942 0 0 0 1 . . .         .
1.6984425 0 0 0 1 . . .  3.352941
 1.698596 0 0 0 1 . . .         0
 1.701429 0 0 0 1 . . .      4.25
 1.701585 0 0 0 1 . . . 1.6923077
1.7026092 0 0 0 1 . . .  3.117647
1.7042558 0 0 0 1 . . .        .5
1.7134767 0 0 0 1 . . .        .8
 1.733449 0 0 0 1 . . .       9.5
 1.737523 0 0 0 1 . . .      2.35
1.7392145 0 0 0 1 . . .         3
1.7411906 0 0 0 1 . . .         0
 1.747248 0 0 0 1 . . .  8.285714
1.7628268 0 0 0 1 . . . 4.5454545
 1.785712 0 0 0 1 . . .  5.666667
1.7897795 0 0 0 1 . . .      3.35
 1.792571 0 0 0 1 . . .    4.6875
1.7940724 0 0 0 1 . . .         0
 1.797435 0 0 0 1 . . .  1.263158
1.8047527 0 0 0 1 . . .  1.840909
1.8117495 0 0 0 1 . . .         7
1.8228152 0 0 0 1 . . .        .5
1.8263686 0 0 0 1 . . .         .
1.8442575 0 0 0 1 . . .         0
 1.865362 0 0 0 1 . . .       4.5
1.8656297 0 0 0 1 . . .       6.5
 1.869146 0 0 0 1 . . . 3.6333334
 1.870972 0 0 0 1 . . .  3.560345
1.8753372 0 0 0 1 . . .         0
1.8757232 0 0 0 1 . . . 2.3783784
end

I am sorry for not having put data example beforehand. The data example contains 100 observations.
Michael

[George Ford]

This help?

sysuse auto, clear
putexcel set statatrial.xlsx, sheet(STATA) replace
reg price i.mpg
matrix B = r(table)'
putexcel A2 = matrix(B) , rownames

[Jared Greathouse]

You can do

dataex, count(800)

and then show the model you estimated. There's also a way to do this using collect, if you want

[Michael Duarte Goncalves]

That's exactly what I need ! Thanks George Ford.

But how can I incorporate that in loop in order to obtain one matrix for any variable i please?
Is that correct?

    forvalues i = 0/3    {
        local ba_hes Bachelor_hes
        foreach c of local ba_hes    {
                svy,subpop(`c'`i'): prop k_uebverl
                matrix B = `r(table)'
                putexcel A2 = matrix(B), rownames // template provided by George Ford, Statalist
        }
    }

Thanks a lot.

[George Ford]

Two options. One is to create a program that you can use any variable of interest. It will add each to the XLSX in a separate page.
The other is to loop.

sysuse auto, clear

capture program drop putmeans
program putmeans
args var
putexcel set statatrial.xlsx, sheet(`var') modify
reg `var' i.mpg
matrix B = r(table)'
putexcel A2 = matrix(B) , rownames
end
putmeans price
putmeans length

foreach var in price weight {
    putexcel set statatrial.xlsx, sheet(`var') modify
    reg `var' i.mpg
    matrix B = r(table)'
    putexcel A2 = matrix(B) , rownames
}

[Michael Duarte Goncalves]

Hi George Ford, thank you so much for the suggestion.

I will try both ways and if I have any problems I will come back to you. Thanks again for your help.

[Michael Duarte Goncalves]

Dear George Ford :

I have a question please.

I chose to do the loop instead of program, I need more time to learn how to use program. I would like to incorporate loops, but maybe too ambitious for my inexperience.

So, everything works perfectly well, thank you.

However, would it be possible to get the matrices for all the variables listed one after the other in the Excel sheet? I tried to do this little loop, but the only values I see in my excel file are from the last regression done by this loop:

    forvalues i = 0/3    {
        local ba_hes Bachelor_hes
        foreach c of local ba_hes    {
            putexcel set Figure11.xlsx, sheet(Figure 11) modify
                svy,subpop(`c'`i'): prop k_uebverl
                matrix B = r(table)'
                putexcel A2 = matrix(B), rownames

        }
    }

[Michael Duarte Goncalves]

And another question (or curiosity) please:

• Why when we want to extract elements from a matrix, we do this code (and as presented in the point #7):
  
  

  	matrix B = r(table)'

Instead of this one:

matrix B = `r(table)'

[Hemanshu Kumar]

r(table) is not a local macro, so you would not put it into a pair of single quotes.
r(table)' is the transpose of the matrix r(table)

[Michael Duarte Goncalves]

Hi Hemanshu Kumar !

Thanks a lot for the clarification!
It makes sense because the original r(table) matrix is r(table) : 9 x 22, and r(table)' is : 22 x 9.

Best,

Michael

[Hemanshu Kumar]

However, would it be possible to get the matrices for all the variables listed one after the other in the Excel sheet? I tried to do this little loop, but the only values I see in my excel file are from the last regression done by this loop:

forvalues i = 0/3 {
local ba_hes Bachelor_hes
foreach c of local ba_hes {
putexcel set Figure11.xlsx, sheet(Figure 11) modify
svy,subpop(`c'`i'): prop k_uebverl
matrix B = r(table)'
putexcel A2 = matrix(B), rownames

}
}

Your problem is that in each iteration of the loop, the matrix is being written at the same location (A2) of the same sheet, unlike the code provided by George.

You could do this:

local row 2
forvalues i = 0/3    {
    local ba_hes Bachelor_hes
    foreach c of local ba_hes    {
        putexcel set Figure11.xlsx, sheet(Figure 11) modify
        svy,subpop(`c'`i'): prop k_uebverl
        matrix B = r(table)'
        putexcel A`row' = matrix(B), rownames
        local row = `row' + rowsof(B) + 2
    }
}

[George Ford]

I transposed it so it looked like the table you posted, with variables in rows not columns.

[Michael Duarte Goncalves]

Thank you Hemanshu Kumar for the excellent explanation. I tried the code and all works perfectly.

Thank you George Ford for the clarification about r(table)and '.