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

19th London Stata Users Group Meeting

Proceedings Coming Soon
Date:
Location:
12-13 September 2013
Cass Business School, City University London, UK
Stata  Stata 13    Timberlake Consultants | Statistics | Econometrics | Forecasting

Contents

 

Back to top 

Overview

The 2013 London Stata Users Group meeting is a two-day international conference where the use of Stata is discussed across a wide-ranging breadth of fields and environments. The meeting will comprise a series of selected presentations and feature presentations from StataCorp representatives.

Established in 1995, the UK meeting is the longest running series of Stata Users meeting. The meeting is open to all interested; in past years, participants have travelled from around the world to attend the event. Representatives from StataCorp will also be in attendance.

We look forward to seeing you at the 2013 London Stata User Group Meeting!

 

Back to top 

Programme

The programme for the 19th Stata Users Group Meeting is now available, subject to minor revisions. Click here to view the programme »

 

Back to top 

Presentation abstracts

Presentation abstracts for the 19th Stata Users Group Meeting are available. Click here to view the confirmed abstracts online or, alternatively, download the abstracts as a PDF here.

 

Back to top 

Scientific Organisers

Stephen P. Jenkins
London School of Economics and Social Policy
s.jenkins@lse.ac.uk

Roger B. Newson
Imperial College London
r.newson@imperial.ac.uk

 

Back to top 

UGM Drinks and Dinner

Informal drinks and dinner will be held on the evening of Thursday 12 September 2013.

Pre-dinner Drinks

Pre-dinner drinks will be held from 6.00pm at Chiswell Street Dining Rooms, a short walk from Cass Business School. See the map below:


View Larger Map

Dinner

Dinner will be held from 7.00pm onward at 3 South Place Bar & Grill. It is a 5 minute walk from Chiswell Street.


View Larger Map

 

Back to top 

Logistics of the Meeting

Logistics are organised by Timberlake Consultants, Authorised Distributors of Stata in the United Kingdom, Ireland, Spain, Portugal, Poland and Brazil. Click here to visit the Stata Section of our website »

Timberlake generously sponsor registration fee waivers for presentations (one fee waiver per presentation, regardless of number of authors involved). We will also pay a small fee to a presenter of a longer review or tutorial paper.

Timberlake can also assist delegates with sourcing accommodation and other general enquiries regarding travelling or stay in London.

Visitors to London might like to know that, by British standards, September is usually relatively dry and warm.

 

Back to top 

Fees & Accommodation

Cost (per participant):

Meeting Fees

 

Non-students - attendance to both days £96.00

 

Non-students - attendance to one day only £66.00

 

Students - attendance to both days £66.00

 

Students - attendance to one day only £48.00

 

UGM Dinner (optional) £36.00
  • All prices include VAT
  • Lunches, refreshments and all meeting materials are included within the registration fees

Timberlake Consultants sponsor registration fee waivers for presentations (one fee waiver per presentation, regardless of number of authors involved). However, presenters must still register their place.

Following registration (either online or directly through the Timberlake UK office), we will contact you to confirm your registration, issue joining instructions for the meeting and where required, complete payment.

Payment can be made by cheque, bank transfer or credit/debit card.

If you need assistance in locating hotel accommodation in the area, please contact our sales and training team.

 

Back to top 

Registration

We welcome delegates to find out more and register interest for the course by contacting our sales and training team either by email: training@timberlake.co.uk or by phone: +44 (0) 20 8697 3377 or by filling out an online registration form.

 

Back to top 

Proceedings of the 18th London Stata Users Group Meeting

The proceedings and presentations from last years Stata Users Group Meeting are available here.

 

Back to top 

Return to  Stata  |  Home

 

 
Newsletter Registration