Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
From | Eric Booth <ebooth@ppri.tamu.edu> |
To | "<statalist@hsphsun2.harvard.edu>" <statalist@hsphsun2.harvard.edu> |
Subject | Re: st: Followup: inverting a stored table of regression results - then exporting to a cvs/excel file |
Date | Wed, 3 Nov 2010 20:07:00 +0000 |
<> 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/