Exporting Stata Results to MS Office
Stata results can be exported in a wide variety of ways. We've divided the approaches into two groups: working with the Results Window and working with estimated parameters directly.
From the Results Window
Copy Table into Excel, Word, or PowerPoint
Suppose you've run svy:mean on 3 variables, and Stata has produced a nice table in the Results Window that you want to capture.
. svy, subpop(subpop): mean var1 var2 var3 (running mean on estimation sample) Survey: Mean estimation Number of strata = 43 Number of obs = 20607 Number of PSUs = 86 Population size = 261897236 Subpop. no. obs = 18081 Subpop. size = 253890607 Design df = 43 -------------------------------------------------------------- | Linearized | Mean Std. Err. [95% Conf. Interval] -------------+------------------------------------------------ var1 | 289.6824 4.175324 281.2621 298.1028 var2 | 106.6606 1.311485 104.0157 109.3055 var3 | 396.343 4.804086 386.6547 406.0314 --------------------------------------------------------------
You can copy the table into Excel or into a Word table and retain much of its formatting:
- Highlight the table in the Results Window.
- Right click on the table and select Copy Table.
- In Excel, click on the cell where you want the upper-left corner of the table.
- Right click and select Paste.
You'll need to adjust column widths to see all the text. We've found that copying the upper half of the table separately from the lower half helps Excel select the right number of columns to use.
To copy into Word, count the number of rows and columns you'll need, create a table of that size, highlight all the cells in the table, and then paste. In the above example, the table of parameter estimates requires 5 columns and 6 rows if you do not include the top and bottom horizontal lines in your highlighting.
You can copy the highlighted table as a picture directly into a Word document. It's a perfect snapshot of the table in the Results Window.
- Highlight the table in the Results Window.
- Drag the left border of the Results Window to the right until the highlighted table just fills the width of the Window.
- Right click on the table and select Copy as Picture.
- In Word or PowerPoint, right click and paste where you want the picture.
You can resize the picture, and you can right click to edit it.
Using the Parameter Estimates
Suppose that we want to make a table of just the means in the above example. We can do this be taking advantage of a very nice feature of Stata. Most Stata commands produce temporary variables containing the key results. These temporary variables will continue to store these values until you use another Stata command that replaces those results, or until you end your Stata session.
You can look at what results have been stored using either return list or for estimation commands ereturn list. Here's a subset of the estimate parameters stored temporarily after the svy:mean command that produced the table above:
ereturn list scalars: e(df_r) = 43 e(N_strata_omit) = 0 e(singleton) = 0 e(census) = 0 e(N_subpop) = 253890607 e(N_sub) = 18081 e(N_pop) = 261897236 e(N_psu) = 86 e(N_strata) = 43 e(N_over) = 1 e(N) = 20607 (lots of results omitted here) matrices: e(b) : 1 x 3 e(V) : 3 x 3 e(_N_subp) : 1 x 3 e(V_srssub) : 3 x 3 e(V_srs) : 3 x 3 e(_N) : 1 x 3 (mores results omitted here)
For purposes of this example, we're only interested in the means. The estimates of the means are contained in a matrix called e(b), which has dimensions 1 x 3.
The matrix e(b) is temporary. We want to create variables that we can export to Excel, so we first need to put the means into variables that won't go away when we run another estimation command. There are lots of ways to do this. We'll demonstrate one way that uses official Stata commands and is flexible, but it requires you to manage the data a bit more than you might care to do. After that we'll talk about a number of user-contributed commands that work with results from estimation commands.
First, let's verify that we have in fact selected the right matrix of estimates:
matrix list e(b) e(b)[1,3] var1 var2 var3 y1 289.68245 106.6606 396.34305
We can compare these values with the means printed in the table above and see that we have the estimates of the means. Next put them into a matrix, and then create a variable for each cell in the matrix:
matrix means = e(b) gen meanvar1= means[1,1] gen meanvar2= means[1,2] gen meanvar3= means[1,3] list meanvar* in 1 +--------------------------------+ | meanvar1 meanvar2 meanvar3 | |--------------------------------| 1. | 289.6824 106.6606 396.343 | +--------------------------------+
keep in 1
(21661 observations deleted)
Since every observation has the same values for the three means, we can keep just one observation.
At this point we can browse the data, copy, and paste it to an empty Excel file template we've created with column and row labels, colors, and all the other formatting we want. We find this to be the easiest approach.
Another approach is to export the results to Excel.
export excel "c:\tables\means.xlsx", firstrow(variables)
Here is an example of how to write a program to build a more complex table with many rows.
New in Stata 13 is the command putexcel. It reads the parameter estimates described above and writes them directly to an Excel spreadsheet. You can specify the starting cell (upper left) and even write column headers. Here's an example that accomplishes the task described above.
svy, subpop(subpop): mean var1 var2 var3
putexcel C4=("Means") B5=matrix(e(b)) using "c:\tables\means.xlsx"
As we saw above, the svy: mean command puts the means into the matrix e(b). We then instructed putexcel to write those 3 means into a row in a spreadsheet, starting with cell B5. We also wrote the label "Means" centered above the 3 means in cell C4. The putexcel command is very powerful and flexible. See the PDF help for full details.
It is a testament to both the cleverness and generosity of the Stata user community that so many powerful open-source commands exist outside the set shipped by Stata Corporation, and further that the authors maintain these commands and continue to offer improved versions. Most of these commands are archived at Boston College in the SSC (Statistical Software Components) website. To read about and install these commands, you can search the SSC website, or you can use the ssc command in Stata.
ssc describe parmest ssc install parmest
CPC maintains the most current version of each of the commands listed below. You will need to install them locally if you run Stata on a standalone computer.
The discussion below is brief, intended only to point you to the command you might need. See the help for each command for details.
The tabout command produces publication-quality cross tabulations. Lots of features are available to customize the table. The output file may be in tab-delimited or html format. Tab-delimited format files may be copied into Word and converted into a table using the Table menu (Table, Convert, Text to table). Html format files can be opened in your browser and copied. Inside Word, use Paste Special and paste the table as Formatted Text (rtf). There's a tutorial on the command as well.
The outreg command uses the saved results after an estimation command to create a text file of the results you select. You can then turn this text file into a Word table. This command has lots of formatting features, and you can combine the results from several regressions into a single table. Here's a simple example to get you started.
cd "c:\tables\" sysuse auto, clear regress mpg foreign weight headroom trunk length turn displacement outreg using outtab1, replace
The output file is tab-delimited and has the extension .out. Open the file in Word, highlight the rows of results, click on Insert, Table, Convert text to table.
This is an extension of outreg to enhance its capabilities with multiple models and to provide more format options. Here's an example from the help. Follow the instructions above to convert the results to a Word table.
cd "c:\tables\" sysuse auto, clear regress mpg foreign weight headroom trunk length turn displacement outreg2 using outtab2, replace cttop(full) regress mpg foreign weight headroom outreg2 using outtab2, see
This command is part of the estout package. The esttab command produces a table of regression results that have been stored by the eststor command, or the current results if nothing has been stored. The output table may be tab, csv, rtf, html, or other formats. Tab-delimited format files may be imported into Excel using the Import Wizard. Rtf files go directly into Word. There's a steep learning curve to this powerful command, but if you produce publication-quality tables often that have to look "just so", it's worth the time to study this command. Here's a simple example that outputs to .rtf.
eststo: svy, subpop(subpop if childage == 1): mean var1 var2 var3 esttab using "c:\tables\esttab_means.rtf", replace
The command gives you a clickable link to esttab_means.rtf in the Results Window. There's a tutorial available for esttab here.
Like esttab, the xml_tab command works from stored estimates. The output is in Excel's xml format. This format allows for a very feature-rich table that takes advantage of many of Excel's capabilities. Here's a simple example.
svy, subpop(subpop): mean enerbev1 enerfood1 enertot1 xml_tab e(b), save("d:\statatemp\xml_tab.xml") replace
The command gives you a clickable link to xml_tab.xml. You can store multiple sets of estimates using estimates store and use xml_tab to combine them in a single table.
The parmest command comes in a package of four modules: parmest, parmby, parmcip, and metaparm. The parmest and parmby commands put each estimation result into a separate observation in an output dataset. parmcip inputs these variables and adds new variables containing confidence intervals and p-values. metaparm does a metanalysis on sets of estimation results.You can see an example here.
The logout command captures the results that are printed to the Results Window (log) and writes them to Excel, Word, or other formats. The success of the formatting in the output file depends on the complexity of the results table you are exporting. Compared with copying from the log by hand and pasting into a spreadsheet, this approach may not produce as well formatted tables, but it can be automated. Here's an example:
logout, save(c:\tables\logout_means) excel replace: /// svy, subpop(subpop): mean var1 var2 var3
This command produces a file call logout_means.xml, and it gives you a clickable link to this file in the Results Window.