st: Followup: inverting a stored table of regression results - then exporting to a cvs/excel file

["Benhoen2" <benhoen2@earthlink.net>]

I just realized 2 more issues

1) there is an error in the code for the loop.  As you can see although the
loop produced correctly named models (e.g., "modelO", "model11") the results
did not change.

Previously I had used this .do file:

sysuse auto, replace
g rcat = int((100)*runiform())
egen catg = cut(rcat),group(4)
tab1 catg
eststo clear
bys catg: eststo: qui regress price mpg weight foreign
estout,cells(b(star fmt(3))) stats (r2_a N depvar, fmt(%5.2f %9.0g))
collabels(none)

which produces this output:

----------------------------------------------------------------------------
                     est1            est2            est3            est4   
----------------------------------------------------------------------------
mpg               132.515         -52.392         147.159         -14.065   
weight              4.334**         4.211*          3.879           2.503*  
foreign          4888.586**      3391.147        3322.140        3981.645** 
_cons          -11453.831       -5648.428       -9647.244       -2368.237   
----------------------------------------------------------------------------
r2_a                 0.61            0.48            0.35            0.44   
N                      18              19              17              20   
depvar              price           price           price           price   
----------------------------------------------------------------------------

but this does not change the model names.  Is there a way to combine the
two?

2) Potentially more important.  When I tried running the loop with a large
number of groups I maxed out the number of stored results.  

"system limit exceeded
you need to drop one or more models
r(1000);"

Any ideas to get all of this into one set of code would be great. 

Ben 


Ben Hoen
LBNL
Office: 845-758-1896
Cell: 718-812-7589
bhoen@lbl.gov


-----Original Message-----
From: Benhoen2 [mailto:benhoen2@earthlink.net] 
Sent: Wednesday, November 03, 2010 3:05 PM
To: 'statalist@hsphsun2.harvard.edu'
Cc: 'Benhoen2'
Subject: inverting a stored table of regression results - then exporting to
a cvs/excel file

Hello statalisters,

I have a .do file that produces a stored set of results:

sysuse auto, replace
g rcat = int((100)*runiform())
egen catg = cut(rcat),group(4)
tab1 catg

eststo clear
qui levelsof catg, local(levels)
qui foreach i of local levels {
	regress price mpg weight foreign
	eststo model`i'
	}
*
estout,cells(b(star fmt(3))) stats (r2_a N depvar, fmt(%5.2f %9.0g))
collabels(none)

This produces this output:


----------------------------------------------------------------------------
                   model0          model1          model2          model3   
----------------------------------------------------------------------------
mpg                21.854          21.854          21.854          21.854   
weight              3.465***        3.465***        3.465***        3.465***
foreign          3673.060***     3673.060***     3673.060***     3673.060***
_cons           -5853.696       -5853.696       -5853.696       -5853.696   
----------------------------------------------------------------------------
r2_a                 0.48            0.48            0.48            0.48   
N                      74              74              74              74   
depvar              price           price           price           price   
----------------------------------------------------------------------------

I will run a similar .do file with a relatively small number of regressors
yet over a relatively large number of groups (n=400).  Ideally I would like
to be able to invert the output so that the regressors are across the top
and the model numbers are rows. Is that possible?  I played around with
"estout matrix(matname)" but was unclear how to apply this.  Any help here
would be great.

Secondly I want to export this output to a csv or excel file for further
work.  Again, ideally I could preserve the format, for instance the stars.
Any ideas?  I was able to clumsily do this by copying and special pasting
"transpose", removing rows etc. into excel, but it would be great if I could
automate at least parts of it. 

Thanks, as always,

Ben
Ben Hoen
LBNL
Office: 845-758-1896
Cell: 718-812-7589
bhoen@lbl.gov



*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/