Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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):

    Code:
    (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:

    Code:
    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 :
    Code:
    type mismatch
    r(109);
    Code:
    
    
    Thank you so much in advance.
    Last edited by Michael Duarte Goncalves; 21 Nov 2022, 07:27.

  • #2
    Data example with code please!

    Comment


    • #3
      Yes of course,
      Code:
      * 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
      Last edited by Michael Duarte Goncalves; 21 Nov 2022, 08:08.

      Comment


      • #4
        This help?

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

        Comment


        • #5
          You can do
          Code:
          dataex, count(800)
          and then show the model you estimated. There's also a way to do this using collect, if you want

          Comment


          • #6
            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?

            Code:
                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.
            Last edited by Michael Duarte Goncalves; 21 Nov 2022, 08:19.

            Comment


            • #7
              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.

              Code:
              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
              }
              Last edited by George Ford; 21 Nov 2022, 08:41.

              Comment


              • #8
                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.

                Comment


                • #9
                  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:

                  Code:
                      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
                  
                          }
                      }


                  Comment


                  • #10
                    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):

                      Code:
                      	matrix B = r(table)'
                    Instead of this one:

                    Code:
                    matrix B = `r(table)'

                    Comment


                    • #11
                      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)

                      Comment


                      • #12
                        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

                        Comment


                        • #13
                          Originally posted by Michael Duarte Goncalves View Post
                          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:

                          Code:
                          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:

                          Code:
                          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
                              }
                          }

                          Comment


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

                            Comment


                            • #15
                              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 '.

                              Comment

                              Working...
                              X