Stata

Stata Features: Export tables to Excel

A new feature in Stata 13, designed by Kevin Crow, putexcel, allows you to easily export matrices, expressions and stored results to an Excel file. Combining putexcel with a Stata command’s stored results allows you to create the table displayed in your Stata Results window in an Excel file, as follows.

A stored result is simply a scalar, macro or matrix stored in memory after you run a Stata command. The two main types of stored results are e-class (for estimation commands) and r-class (for general commands). You can list a command’s stored results after it has been run by typing ereturn list (for estimation commands) or return list (for general commands). Here’s a simple example by loading the auto dataset and running correlate on the variables foreign and mpg:

. sysuse auto
(1978 Automobile Data)

. correlate foreign mpg
(obs=74)

             |  foreign      mpg
-------------+------------------
     foreign |   1.0000
         mpg |   0.3934   1.0000

Because correlate is not an estimation command, use the return list command to see its stored results.

. return list

scalars:
                  r(N) =  74
                r(rho) =  .3933974152205484

matrices:
                  r(C) :  2 x 2

Now you can use putexcel to export these results to Excel. The basic syntax of putexcel is:

putexcel excel_cell=(expression) … using filename [, options]

If you are working with matrices, the syntax is:

putexcel excel_cell=matrix(expression) … using filename [, options]

It’s easy to build the above syntax in the putexcel dialog (there’s also helpful YouTube tutorial about the dialog here). List the matrix r(C) to show the below:

. matrix list r(C)

symmetric r(C)[2,2]
           foreign        mpg
foreign          1
    mpg  .39339742          1

To re-create the table in Excel, you need to export the matrix r(C) with the matrix row and column names. The command to type in your Stata Command window is:

putexcel A1=matrix(r(C), names) using corr

Note that to export the matrix row and column names, the example used the names option after we specifed the matrix r(C). When corr.xlsx file is opened in Excel, the table below is displayed:

Producing Excel tables with Stata 1

Next let’s try a more involved example. Load the auto dataset, and run a tabulation on the variable foreign. Because tabulate is not an estimation command, use the return list command to see its stored results.

. sysuse auto
(1978 Automobile Data)

. tabulate foreign

   Car type |      Freq.     Percent        Cum.
------------+-----------------------------------
   Domestic |         52       70.27       70.27
    Foreign |         22       29.73      100.00
------------+-----------------------------------
      Total |         74      100.00

. return list

scalars:
                  r(N) =  74
                  r(r) =  2

tabulate is different from most commands in Stata in that it does not automatically save all the results we need into the stored results (we will use scalar r(N)). The matcell() and matrow() options of tabulate are used to save the results produced by the command into two Stata matrices.

. tabulate foreign, matcell(freq) matrow(names)

   Car type |      Freq.     Percent        Cum.
------------+-----------------------------------
   Domestic |         52       70.27       70.27
    Foreign |         22       29.73      100.00
------------+-----------------------------------
      Total |         74      100.00

. matrix list freq

freq[2,1]
    c1
r1  52
r2  22

. matrix list names

names[2,1]
    c1
r1   0
r2   1

The putexcel commands used to create a basic tabulation table in Excel column 1 row 1 are:

putexcel A1=("Car type") B1=("Freq.") C1=("Percent") using results, replace
putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N)) using results,
     modify

Below is the table produced in Excel using these commands:

Producing Excel tables with Stata 1

Again this is a basic tabulation table. You probably noticed that the Cum. column or the Total row in the export table are not displayed. Also the Car type column contains the numeric values (0,1), not the value labels (Domestic, Foreign) of the variable foreign and that the Percent column is not formatted correctly. To get the exact table displayed in the Results window into an Excel file takes a little programming. With a few functions and a forvalues loop, users can easily export any table produced by running the tabulate command on a numeric variable.

There are two extended macro functions, label and display, that can help with this. The label function can extract the value labels for each variable and the display function can correctly format numbers for the numeric columns. Last, you can use forvalues to loop over the rows of the returned matrices to produce the final tables.




 

Back to top 

Return to:  Stata Features  |  Stata  |  Home

Data Analysis and Statistical Software  

Stata 13

Stata  Stata Release 13
Stata 13 DVD and Sleeve Developer:
Latest release:
Operating systems:
StataCorp LP
13 (June 2013)
Windows; Mac OS; Linux
New — Treatment effects • Multilevel mixed-effects GLM • Power and sample size • Multilevel SEM with generalised outcomes • Forecasting • Long strings and BLOBs • and much more...
Overview New Features Screenshots System Prices Stata Manuals Release Notes Students  

Overview

Stata 13 is a complete, integrated statistical package that provides everything you need for data analysis, data management, and graphics. Stata is not sold in modules, which means you get everything you need in one package. And, you can choose a perpetual licence, with nothing more to buy ever. Annual licences are also available.

Click here to explore all of Stata’s features.

 

Back to top 

New Features in Stata 13

Stata 13 has 77 new features, such as Binary Large Objects (BLOBs), Treatment-effect estimators, Multilevel mixed-effect models, Forecasts and Project Manager. Use the following links to learn more about the new features or click here for a full list of new Stata features.

Additionally, you can also view the official Stata 13 Tour video.

 

Back to top 

Stata 13 Screenshots

(To view larger versions of the screenshots, click on the images below to view in a new tab).

Stata 13 Windows

Stata 13 windows

Forecast Graph

Forecast graph

Power and sample size graph

Power and sample size graph

Structural Equation Model (SEM) Builder

Structural Equation Model (SEM) Builder

 

Back to top 

System Requirements

Stata 13 runs on dual-core, multi-core and multi-processor computer (for the full list of supported platforms, click here). You can also check to see if your computer is compatible using the links in this section:

Stata for Windows

  • Windows 8 *
  • Windows 7 *
  • Windows Vista *
  • Windows XP Pro/Home *
  • Windows Server 2008 *
  • Windows Server 2003 *
  • Windows 2000 Pro/Server *

* 32-bit and 64-bit Windows varieties for x86, x86-64, and Itanium.

Find out if your OS is 64-bit compliant »

Stata for Mac

  • 32-bit Stata for any Mac running Mac OS X 10.5 or greater;
    (Universal binary for all processors including Intel and PowerPC);
  • 64-bit Stata requires Intel-based Mac with a 64-bit processor and Mac OS X 10.5 or greater.

Find out if your OS is 64-bit compliant »

Stata for Unix

  • Linux: Any 32-bit (x86 or compatible) or 64-bit (Intel Itanium (x86-64 or compatible) running Linux;
  • IBM AIX:Any RS/6000 series running 64-bit IBM AIX;

Hardware requirements

  • Minimum of 512 MB of RAM;
  • Minimum of 80 MB of disk space;
  • Stata for Unix requires a video card that can display thousands of colours or more (16-bit or 24-bit colour).

 

Back to top 

Prices

Stata Prices  
Commercial Click here to view
Academic Click here to view

To find out more information and to order your copy of Stata, please contact our sales team either by email, phone or by filling out an online request form.

 

Back to top 

Stata Manuals

Stata 13 is a big release. The number of manual pages increases by 16%, from 9,493 pages in 17 manuals to over 11,000 pages in 20 manuals.

Manuals are available in PDF form. In Stata 13, printed manuals will no longer be available.

Click here to view all 20 Stata 13 manuals »

The Stata 13 documentation is copyright of StataCorp LP, College Station TX, USA, and is used with permission of StataCorp LP.

 

Back to top 

Stata 13 Release Notes

View Stata 13 release notes here (PDF) »




 

Back to top 

Return to:  Home

 

 
Newsletter Registration