Tutorial:
Automated table generation and reporting with Stata
---------------------------------------------------------------
Ben Jann, ETH Zurich, jannb@ethz.ch
November 2008
Required user packages:
- mat2txt <install> - estwrite <install> - estout <install>
<next> <PDF version> ---------------------------------------------------------------
---------------------------------------------------------------
Outline
o Introduction
o Part 1: Low-level results processing
- How to access results from Stata routines - Getting things out of Stata: The file command - Wrappers
o Part 2: Handling model estimation results
- Results from "estimation" commands are special - Archiving models - Tabulating estimation results
o Part 3: Automatic reporting
- Automation - Example with LaTeX - Example with MS Word and Excel
<next> <top> ---------------------------------------------------------------
---------------------------------------------------------------
Introduction I
Statistical software packages are good at analyzing data, but they are often weak when it comes to reporting.
o Output from statistical routines contains all sorts of details that are valuable to the researcher but are not so important for reporting.
=> you have to select relevant results
o Output from statistical routines sometimes contains results that are not well suited for interpretation or for presentation to a non-expert audience.
=> you have to transform results
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Introduction II
o Output from statistical routines is often not well formatted for presentation.
=> you have to rearrange and reformat results
o Various software packages might be used for further processing of results and for reporting.
=> you have to transfer results to specific file formats
o You might need to re-use results for other reports or extract additional results at a later point in time.
=> you have to archive results
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Introduction III
+--------------+ | TWO MAXIMS | +--------------+
1) Never Copy/Paste results by hand
You will almost surely make tons of mistakes!
2) Do everything only once
It is simply a waste of time to do things more than once.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Introduction IV
o These two goals can be reached by automation.
o Automation has its price:
- initial investment of time and effort
- reduced flexibility
o However, personally I find that automation almost always pays off.
o For example, although you are convinced that you do the tables in your research paper only once, you'll find yourself doing them over, and over, and over, ...
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Introduction V
o Furthermore, automation increases quality:
- no copy/paste errors
- errors and possible improvements are often detected after everything is done; in a non-automated settings there are high barriers against correcting such errors or implementing the improvements
- the lack of flexibility leads to standardization (which is usually positive, but can sometimes also hinder innovation)
- automation makes research more replicable
o Moreover, good tools can lower the costs of automation dramatically.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Introduction VI
o Of course, there are also exceptions where automation might not be worth the effort.
o Examples:
- slides for presentations that are only used once or twice
- numbers in text body (trick: only cite approximate values)
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Part 1: Low-level results processing
o How to access results from Stata routines
o Getting things out of Stata: The file command
o Wrappers
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Accessing results in Stata I
o A prerequisite for automation is that the results from statistical routines can be accessed by the user.
o In Stata, most commands return their results in r() or e() (see return).
- r() is used by "general" commands such as summarize - e() is used by "estimation" commands such as regress
o Returned are:
- string scalars - numeric scalars - numeric matrices
- For example, estimation commands return the number of observations in e(N), the name of the command in e(cmd), and the coefficients vector and the variance matrix in e(b) and e(V).
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Accessing results in Stata II
o Use return list or ereturn list to find out about available returns. Use matrix list to see the contents of a returned matrix.
INCLUDE help tabletutorial_1 <run>
INCLUDE help tabletutorial_2 <run>
o Use matrix list to see the contents of a returned matrix.
INCLUDE help tabletutorial_3 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Accessing results in Stata III
o You can use the e() and r() scalars and matrices more or less as you would use any other scalar or matrix, although it is often advisable to first copy the results into regular macros, scalars, or matrices (see macro, scalar, and matrix).
o Examples:
INCLUDE help tabletutorial_4 <run>
INCLUDE help tabletutorial_5 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Accessing results in Stata IV
o Example with matrices:
INCLUDE help tabletutorial_6 <run>
INCLUDE help tabletutorial_7 <run>
o Note that coefficients and standard errors can also be accessed as _b[] and _se[]:
INCLUDE help tabletutorial_8 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Getting things out of Stata: The file command I
o The file command is used in Stata to write to (or read from) a file on disk.
o Use file to produce custom output files.
o file is a low level command. It just writes plain text, line by line. You have to do all formatting yourself.
o file may appear a bit clumsy: You have to
file open handle using filename, write /*initialize*/
file write handle ... /*write*/ ...
file close handle /*done*/
o However, file can produce any desired output.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Getting things out of Stata: The file command II
o Example: Write a tab delimited file containing descriptive statistics
INCLUDE help tabletutorial_9 <run> <show>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Getting things out of Stata: The file command III
o This can easily be turned into a program:
INCLUDE help tabletutorial_11 <run> <show>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Getting things out of Stata: The file command IV
o Or let's do HTML:
INCLUDE help tabletutorial_12 <run> <show>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Wrappers
o Of course you do not have to write a new program for everything.
o Check the SSC Archive to find out whether anything already exists that serves your needs (see findit and ssc).
o For example, mat2txt can be used to write a matrix to a tab-delimited file:
INCLUDE help tabletutorial_13 <run> <show>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Part 2: Handling model estimation results
o Results from "estimation" commands are special
o Archiving models
o Tabulating estimation results
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Results from "estimation" commands are special
o Results from e-class commands are special because they share a common structure:
- a coefficients vector: e(b)
- and a variance matrix: e(V)
o There is, to some degree, a consensus/common practice of how to design tables containing model estimation results.
o Many models are estimated, usually, and estimation may be computationally intensive so that archiving the results is an issue.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Archiving models I
o A good approach is to keep model estimation and reporting two separate processes.
o This requires that model estimates are stored for later tabulation.
o Estimating a new model replaces the e()-returns of a previous model. However, the results can be stored in memory under a specific name using estimates store or the eststo user command.
o In Stata 10, it is also possible to save the results of a model on disk using estimates save.
o A problem with estimates save is that it can only store one model at the time (i.e. each model is saved in a separate file). However, the estwrite user command overcomes this problem.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Archiving models II
o Example:
INCLUDE help tabletutorial_14 <run>
INCLUDE help tabletutorial_15 <run>
o Two weeks later:
INCLUDE help tabletutorial_16 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results I
o Various commands exists to compile and export tables of model estimates. estout is one of them. Others are outreg (John Luke Gallup), outreg2 (Roy Wada), xml_tab (Lokshin & Sajaia), outtex (Antoine Terracol), est2tex (Marc Muendler), mktab (Nicholas Winter), parmest (Roger Newson), of which all have their pros and cons.
o The estout package contains four commands:
esttab: User-friendly command to produce publication-style regression tables for screen display or in various export formats such as such as CSV, RTF, HTML, or LaTeX.
estout: Generic program to compile regression tables (the engine behind esttab).
estadd: Program to add extra results (such as e.g., beta coefficients) to e() so that they can be tabulated.
eststo: Improved version of estimates store.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results II
o esttab and estout are very flexible and can produce all sorts of regression tables.
o I will only show a few basic examples here. Many more examples can be found at the following website:
http://repec.org/bocode/e/estout
o The basic procedure is to store a number of models and then apply esttab (or estout) to tabulate them:
INCLUDE help tabletutorial_17 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results III
o esttab can either display the table in Stata's results window or export it to a file on disk using one of several formats, such as
- fixed: fixed-format ASCII
- tab: tab-delimited ASCII
- csv: CSV (Comma Separated Value format) for use with MS Excel
- rtf: Rich Text Format for use with word processors
- tex: LaTeX format
- ...
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results IV
o Use with MS Excel: csv or scsv
INCLUDE help tabletutorial_18 <run> <show>
INCLUDE help tabletutorial_19 <run> <show>
(The scsv format uses a semi-colon as delimiter which is appropriate for certain language versions of Excel.)
o Use the plain option if you intend to do additional computations in MS Excel:
INCLUDE help tabletutorial_20 <run> <show>
(No XML support. Sorry.)
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results V
o Use with MS Word: rtf
INCLUDE help tabletutorial_21 <run> <show>
o Appending is possible. Furthermore, use varwidth(#) and modelwidth(#) to change column widths:
INCLUDE help tabletutorial_21b <run> <show>
o Including RTF literals:
INCLUDE help tabletutorial_21c <run> <show>
INCLUDE help tabletutorial_21d <run> <show>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results VI
o Use with LaTeX: tex
INCLUDE help tabletutorial_22 <run> For a preview, click <texify> to compile the file (LaTeX required) and then <show PDF>
to view the result. If <texify> does not work, click <show PDF from web> to download and view a precompiled version.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results VII
o Improved LaTeX table using the booktabs package:
INCLUDE help tabletutorial_24 <run> <texify> <show PDF> <show PDF from web>
o Improved LaTeX table using the dcolumn package:
INCLUDE help tabletutorial_25 <run> <texify> <show PDF> <show PDF from web>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results VIII
o Advanced LaTeX example
INCLUDE help tabletutorial_26 <run> <texify> <show PDF> <show PDF from web>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Tabulating estimation results IX
o esttab can be used to tabulate any results, not just regression models, as long as they are posted in e() in an appropriate way.
o Example: descriptives table
INCLUDE help tabletutorial_27 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Part 3: Automatic reporting
o Automation
o Example with LaTeX o Example with MS Word and Excel
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Automation
o Automatic reporting means that results and information on formatting should be separated.
o It has to be possible to replace the data without losing the formatting.
o The usual approach is to maintain a hand-edited master file that structures the document and sets the formatting etc. and then dynamically link the files containing results into this file.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Example with LaTeX I
o Step 1: Set up a master file
INCLUDE help tabletutorial_28 <run>
(Of course you would, usually, set up a master file in a text editor, not in Stata.)
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Example with LaTeX II
o Step 2: Generate table files
INCLUDE help tabletutorial_29 <run>
o Step 3: Compile the document
<texify> <show PDF> <show PDF from web>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Example with LaTeX III
o You can now easily replace the tables and recompile the document
INCLUDE help tabletutorial_31 <run>
<texify> <show PDF> <show PDF from web>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Example with MS Word and Excel I
o Such automation does not seem to be easily possible with MS Word.
o However, you can link data files into Excel and then dynamically link Excel tables into Word.
o Step 1: Generate results files in tab-delimited format
INCLUDE help tabletutorial_32 <run>
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Example with MS Word and Excel II
o Step 2: Link data into Excel ("Data" > "Import External Data" > "Import Data..."; locate and select the "_example.txt" file; go through the "Text Import Wizard" and click "Finish"; on the last dialog, called "Import Data", click "Properties...", uncheck "Prompt for file name on refresh" and check "Refresh data on file open", possibly uncheck "Adjust column width").
o Step 3: Format the table in Excel.
o Step 4: Mark and copy the table in Excel and, in Word, paste the table as an Excel object ("Edit" > "Paste Special..."; make sure to select "Paste link").
o Save the Excel and Word documents.
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
Example with MS Word and Excel III
o You can now replace the results files ...
INCLUDE help tabletutorial_33 <run>
o ... open the Excel file and click "Enable automatic refresh" ... o ... open the Word document, mark the section containing the table, and hit "F9" ("Edit" > "Update Link") to update the table.
(The default settings can be changed so that updating occurs automatically when opening the file: "Tools" > "Options" > "General" > check "Update automatic links at open")
<next> <overview> ---------------------------------------------------------------
---------------------------------------------------------------
End of tutorial
o Clean-up: erase working files
INCLUDE help tabletutorial_cleanup <run>
<top> ---------------------------------------------------------------