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

Other Statistical Methods

FIND OUT WHAT'S New in Stata 11

Sample size and power determination

  • Two-sample test of equality of means
  • One-sample test of means
  • Two-sample test of equality of proportions
  • One-sample test of proportions
  • Repeated measurements of the outcome on each experimental unit
  • Survival studies
Generalized method of moments (GMM) New
  • Linear and nonlinear models
  • One-step, two-step, and iterative estimators
  • Cross-sectional, time-series, and panel models
  • Easily specify panel-style instruments
  • Interactive and programmable versions
Nonlinear least-squares regression

Nonlinear seemingly unrelated regression

Stepwise regression
  • Linear
  • Complementary log-log
  • Cox
  • GLM
  • Interval
  • Logistic
  • Conditional logistic
  • Negative binomial
  • Ordered logit
  • Ordered probit
  • Poisson
  • Probit
  • Quantile
  • Skewed logistic
  • Tobit and censored-normal
  • Exponential, Weibull, Gompertz, lognormal, loglogistic, generalized gamma
Nested-model statistics

Orthogonal polynomials

Cronbach’s alpha

Kappa measure of interrater agreement

Kernel-density estimation

Box–Cox transform
  • Can be applied to the left-hand side, right-hand side, or both
  • Parameters can be the same or different
  • Maximum likelihood
  • Zero-skewness log
Power transforms
  • Search for power transform that converts a variable into a normally distributed variable
  • Graphical display of a power-transformed variable
Tests of normality
  • Shapiro–Wilk
  • Shapiro–Francia
  • Skewness and kurtosis test (D’Agostino, with and without Royston correction)
  • Doornik–Hansen New
  • Henze–Zirkler New
  • Two by Mardia New
Collecting statistics into a dataset
  • Collection from any command
  • Collection of results for each group or subgroup of observations
  • Collection from user-written or “official” commands
Drawing samples from multivariate normal distribution

Creating datasets with specified correlation structure

Rolling and recursive analyses

Mathematical functions
  • Absolute value
  • Arccosine returning radians
  • Arcsine returning radians
  • Arctangent returning radians
  • Two-argument arctangent
  • Sine of radians
  • Tangent of radians
  • Cosine of radians
  • Combinatorial function
  • Exponentiation
  • Natural logarithm
  • Natural log factorial
  • ln(gamma(x)) (ln((x−1)!) for integer x)
  • Base 10 logarithm
  • Modulus of x with respect to y
  • Square root
  • Digamma function
  • Trigamma function
  • Hyperbolic sine New
  • Hyperbolic cosine New
  • Hyperbolic arcsine New
  • Hyperbolic arccosine New
  • Hyperbolic tangent
  • Log of odds ratio
  • Inverse log of odds ratio
  • Complementary log-log
  • Inverse complementary log-log
  • Ceiling
  • Floor
  • Minimum
  • Maximum
  • Relative difference
  • Running sum
  • Sign
Statistical functions
  • Binomial cumulative probability New
  • Binomial probability New
  • Cumulative and inverse F distribution
  • F density
  • Incomplete gamma
  • Inverse incomplete gamma
  • Gamma density
  • Incomplete beta
  • Noncentral beta density
  • Inverse binomial
  • Cumulative and inverse chi-squared
  • Inverse cumulative noncentral chi-squared
  • Upper tail of cumulative beta distribution
  • Inverse tail of cumulative beta distribution
  • Upper tail of cumulative gamma distribution
  • Inverse tail of cumulative gamma distribution
  • Upper tail of right cumulative binomial
  • Cumulative noncentral chi-squared
  • Standard normal density
  • Cumulative normal
  • Inverse cumulative normal
  • Bivariate normal
  • Cumulative bivariate normal
  • Student’s 2-tailed t distribution
  • t density
  • Inverse 2-tailed cumulative t distribution
  • Noncentrality parameter L for noncentral chi-squared
  • First and second derivatives of gamma
  • Noncentral F
  • Hypergeometric cumulative probability New
  • Hypergeometric probability New
  • Negative binomial cumulative probability New
  • Negative binomial probability New
  • Negative binomial reverse cumulative probability New
  • Inverse cumulative negative binomial probability New
  • Inverse tail of cumulative negative binomial probability New
  • Poisson cumulative probability New
  • Poisson probability New
  • Poisson reverse cumulative probability New
  • Inverse cumulative Poisson probability New
  • Inverse tail of cumulative Poisson probability New
« back to Capabilites

 

 
Newsletter Registration