Help with outputting Mixed command results to Excel

[Dudi Balson]

Hello,

I hope you could help me, I have spent days trying to figure this out. I am trying to output the mixed command results to Excel. More specifically, I am trying to output the Random effect parameters (I would like to get the estat recovariance parameters outputted to Excel).

I have tried the outreg2 command, esttab and capture methods, but non had worked or outputted the table I need.

Thank you,
Dudi

[Andrew Musau]

https://stats.idre.ucla.edu/stata/fa...using-_diparm/

[Dudi Balson]

Andrew, thank you very much for your reply.
Unfortunately, using that method I do not get the same estimates as with 'estat recovariance'. Also, I am not able to export it to Excel again.

[Bruce Weaver]

Hello Dudi. Does this example help at all? It uses -logistic-, not -mixed-. But it might give you the general idea.

[Dudi Balson]

Bruce, thank you for your help.
No, it does, -mixed- does not have the function etable

[Andrew Musau]

Do you just need the coefficient?

webuse pig, clear
qui  mixed weight week || id:
estat recovariance
mat cov= r(Cov2)
esttab mat(cov) using myfile.xls

[Dudi Balson]

Thank you Andrew.

It seems to work, any idea how i extract 5 coefficient and the residual mixed y || x1:|| x2: || x3 || x4: ? I guess I need to change something in the 'mat cov= r(Cov2)'

[Andrew Musau]

Do you have a 5 level nested model? Then you should have 4 coefficients. You want something like below

mixed y || x1:|| x2: || x3 || x4:
*RESIDUAL VARIANCE
mat var_res= exp(e(b)[1, colnumb(e(b), "lnsig_e:_cons")])^2

*RE COVARIANCES
estat recovariance
forval i=2/5{
mat cov`i'=r(Cov`i')
}
mat cov=var_res\cov2\cov3\cov4\cov5
mat rownames cov = var(Residual) var(level2) var(level3) var(level4) var(level5)
esttab mat(cov) using myfile.xls

[Dudi Balson]

Hello again, Andrew, thank you for your help. Unfortunately, I got the following error (I did try to play with it but could not solve it). The line
'mat var_res= exp(e(b)[1, colnumb(e(b), "lnsig_e:_cons")])^2 '
returns the following error: invalid syntax r(198); Thank you again for your help

[Andrew Musau]

You need to copy and paste the entire output from Stata (including the full set of commands). Otherwise, it's impossible to tell what is wrong. This works for me.

. webuse pig
(Longitudinal analysis of pig weights)

. mixed weight week || id:

Performing EM optimization:

Performing gradient-based optimization:

Iteration 0:   log likelihood = -1014.9268  
Iteration 1:   log likelihood = -1014.9268  

Computing standard errors:

Mixed-effects ML regression                     Number of obs     =        432
Group variable: id                              Number of groups  =         48

                                                Obs per group:
                                                              min =          9
                                                              avg =        9.0
                                                              max =          9

                                                Wald chi2(1)      =   25337.49
Log likelihood = -1014.9268                     Prob > chi2       =     0.0000

------------------------------------------------------------------------------
      weight |      Coef.   Std. Err.      z    P>|z|     [95% Conf. Interval]
-------------+----------------------------------------------------------------
        week |   6.209896   .0390124   159.18   0.000     6.133433    6.286359
       _cons |   19.35561   .5974059    32.40   0.000     18.18472    20.52651
------------------------------------------------------------------------------

------------------------------------------------------------------------------
  Random-effects Parameters  |   Estimate   Std. Err.     [95% Conf. Interval]
-----------------------------+------------------------------------------------
id: Identity                 |
                  var(_cons) |   14.81751   3.124226      9.801716    22.40002
-----------------------------+------------------------------------------------
               var(Residual) |   4.383264   .3163348      3.805112     5.04926
------------------------------------------------------------------------------
LR test vs. linear model: chibar2(01) = 472.65        Prob >= chibar2 = 0.0000

. mat var_res= exp(e(b)[1, colnumb(e(b), "lnsig_e:_cons")])^2

. mat l var_res

symmetric var_res[1,1]
           c1
r1  4.3832637

[Dudi Balson]

Thank you Andrew. Attached is the code.

. mixed y || x1: || x2: || x3: || x4:

Performing EM optimization: 

Performing gradient-based optimization: 

Iteration 0:   log likelihood =  1601.7013  
Iteration 1:   log likelihood =   1601.727  
Iteration 2:   log likelihood =  1601.7271  

Computing standard errors:

Mixed-effects ML regression                     Number of obs     =        940

-------------------------------------------------------------
                |     No. of       Observations per Group
 Group Variable |     Groups    Minimum    Average    Maximum
----------------+--------------------------------------------
          x1 |         58          2       16.2         91
          x2 |        171          2        5.5         12
   x3 |        271          2        3.5         10
   x4 |        332          1        2.8          9
-------------------------------------------------------------

                                                Wald chi2(0)      =          .
Log likelihood =  1601.7271                     Prob > chi2       =          .

-------------------------------------------------------------------------------------
y |      Coef.   Std. Err.      z    P>|z|     [95% Conf. Interval]
--------------------+----------------------------------------------------------------
              _cons |    .070531   .0053958    13.07   0.000     .0599554    .0811065
-------------------------------------------------------------------------------------

------------------------------------------------------------------------------
  Random-effects Parameters  |   Estimate   Std. Err.     [95% Conf. Interval]
-----------------------------+------------------------------------------------
x1: Identity              |
                  var(_cons) |     .00063   .0002483       .000291    .0013639
-----------------------------+------------------------------------------------
x2: Identity              |
                  var(_cons) |   .0010538   .0003623      .0005372    .0020673
-----------------------------+------------------------------------------------
x3: Identity       |
                  var(_cons) |   .0011359   .0003524      .0006183    .0020865
-----------------------------+------------------------------------------------
x4: Identity       |
                  var(_cons) |   .0009812   .0002465      .0005996    .0016056
-----------------------------+------------------------------------------------
               var(Residual) |   .0008576    .000049      .0007667    .0009594
------------------------------------------------------------------------------
LR test vs. linear model: chi2(4) = 818.57                Prob > chi2 = 0.0000

Note: LR test is conservative and provided only for reference.

. 
end of do-file

. do "/var/folders/h1/zpcl3pqj4jj74ktqjmpjfl2w0000gp/T//SD04302.000000"

. mat var_res= exp(e(b)[1, colnumb(e(b), "lnsig_e:_cons")])^2
invalid syntax
r(198);

end of do-file

r(198);

[Andrew Musau]

Strange that you get an invalid syntax error. Can you run the following and copy and paste what you get.

mixed y || x1: || x2: || x3: || x4:
mat l e(b)
di _b[lnsig_e:_cons]
di exp(_b[lnsig_e:_cons])^2

[Dudi Balson]

Thank you Andrew, sorry for the delay.

. mat l e(b)

e(b)[1,6]
     y:     lns1_1_1:     lns2_1_1:     lns3_1_1:     lns4_1_1:      lnsig_e:
           _cons         _cons         _cons         _cons         _cons         _cons
y1     .07053098    -3.6849256    -3.4276736    -3.3901789     -3.463374    -3.5306597

. di _b[lnsig_e:_cons]
-3.5306597

. di exp(_b[lnsig_e:_cons])^2
.00085765

[Andrew Musau]

Try

mixed y || x1:|| x2: || x3 || x4:
*RESIDUAL VARIANCE
mat var_res= exp(_b[lnsig_e:_cons])^2
*RE COVARIANCES
estat recovariance
forval i=2/5{
mat cov`i'=r(Cov`i')
}
mat cov=var_res\cov2\cov3\cov4\cov5
mat rownames cov = var(Residual) var(level2) var(level3) var(level4) var(level5)
esttab mat(cov) using myfile.xls

[Dudi Balson]

Thank you Andrew, it does input it to Excel now!
The only issue is that 'var(level2) .00063' is in the same cell now. Is there any way I can separate them? or I need to use VBA to do that?

Thanks again!