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

[Eric Booth <ebooth@ppri.tamu.edu>]

<>

Also, to get the second part of the -estout- table to append vertically, not horizontally, you can use -stack- (from SSC):

Replace the preserve/restore section in my prev. example with:


**!   
preserve
clear
insheet using "test.txt", tab nonames
sxpose, clear
stack _var1-_var16, into(v1-v8) clear
drop _stack    
drop in 202   //drops the table title vars//
outsheet using "test_transposed.xls", replace nonames
restore
**!

- Eric

__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
ebooth@ppri.tamu.edu



On Nov 3, 2010, at 2:45 PM, Eric Booth wrote:

> <>
> 
> For issue #(1), changing the -bys- to if catg==`i' in my previous example seems to fix your issue.
> 
> Issue #2 is due to the limit of 300 stored estimates (see -help limits-), so for 400 results, you could break up your loop to run models 1/200 and 201/400 and then append them before you -sxpose-.
> This should do the trick:
> 
> *******************!
> estimates clear  
> sysuse auto, clear 
> expand 1000
> g rcat = int((1000)*runiform())
> **Create 400 groups for tables**
> egen catg = cut(rcat),group(400)
> tab1 catg
> 
> qui levelsof catg, local(levels)
> forval i = 1/200 {
> 	qui regress price mpg weight foreign ///
> 	   if catg==`i'   // I changed this
> 	qui eststo model`i'
> 	}
> ****
> estout * using "test.txt" , cells(b(star fmt(3))) ///
> stats(r2_a N depvar, fmt(%5.2f %9.0g))  ///
> collabels(none)  replace style(tab)
> 
> est clear
> forval i = 201/399 {
> 	qui regress price mpg weight foreign ///
> 	   if catg==`i'   // I changed this
> 	qui eststo model`i'
> 	}
> ****
> estout * using "test.txt" , cells(b(star fmt(3))) ///
> stats(r2_a N depvar, fmt(%5.2f %9.0g))  ///
> collabels(none)  append style(tab)
> 
> 
> preserve
> clear
> insheet using "test.txt", tab nonames
> sxpose, clear
> outsheet using "test_transposed.xls", replace nonames
> restore
> 
> 
> 
> //open tables in Windows or Mac OSX//
> if "`c(os)'" == "MacOSX" local i open
> if "`c(os)'" == "Windows" local i start
> !`i' "test_transposed.xls"
> 
> ****************!
> 
> 
> - Eric
> 
> __
> Eric A. Booth
> Public Policy Research Institute
> Texas A&M University
> ebooth@ppri.tamu.edu
> Office: +979.845.6754
> Fax: +979.845.0249
> http://ppri.tamu.edu
> 
> 
> On Nov 3, 2010, at 2:33 PM, Benhoen2 wrote:
> 
>> 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/