Home > Programming > Export tables to Excel

Export tables to Excel

There is a new command in Stata 13, putexcel, that 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.

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) and return list (for general commands). Let’s try 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 we 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 is easy to build the above syntax in the putexcel dialog. There is a helpful video on Youtube about the dialog here. Let’s list the matrix r(C) to see what it contains.

. matrix list r(C)

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

To re-create the table in Excel, we 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, we used the names option after we specifed the matrix r(C). When I open the file corr.xlsx in Excel, the table below is displayed.

results0

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)). We need to use the matcell() and matrow() options of tabulate 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 by these commands.

results1

Again this is a basic tabulation table. You probably noticed that we did not have the Cum. column or the Total row in the export table. Also our Car type column contains the numeric values (0,1), not the value lables (Domestic, Foreign) of the variable foreign, and our 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, we 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 us. The label function can extract the value labels for each variable, and the display function can correctly format numbers for our numeric columns. Last, we use forvalues to loop over the rows of the returned matrices to produce our final tables. Our do-file to produce the tabulate table in Excel looks like

sysuse auto
tabulate foreign, matcell(freq) matrow(names)

putexcel A1=("Car type") B1=("Freq.") C1=("Percent") D1=("Cum.") using results, replace

local rows = rowsof(names)
local row = 2
local cum_percent = 0

forvalues i = 1/`rows' {

        local val = names[`i',1]
        local val_lab : label (foreign) `val'

        local freq_val = freq[`i',1]

        local percent_val = `freq_val'/`r(N)'*100
        local percent_val : display %9.2f `percent_val'

        local cum_percent : display %9.2f (`cum_percent' + `percent_val')

        putexcel A`row'=("`val_lab'") B`row'=(`freq_val') C`row'=(`percent_val') ///
                D`row'=(`cum_percent') using results, modify
        local row = `row' + 1
}

putexcel A`row'=("Total") B`row'=(r(N)) C`row'=(100.00) using results, modify

The above commands produce this table in Excel:

results2

The solution above works well for this one table, but what if we need to export the tabulation table for 100 variables to the same Excel spreadsheet? It would be very tedious to run the same do-file 100 times, each time changing the cell and row numbers. Now we could easily change our do-file into the Stata command (ado-file) called tab2xl. The syntax for our new command could be

tab2xl varname using filename, row(rownumber) col(colnumber) [replace sheet(name)]

The pseudocode of our program (file tab2xl.ado) looks like

program tab2xl
  /* parse command syntax */

  /* tabulate varname */

  /* get column letters based on starting column number passed in */

  /* write header row to filename in starting row number passed in */

  /* loop over rows of returned matrix and calculate/write values to filename */

  /* write total row to filename */
end

If you would like to download a working version of our tab2xl command, type

net install http://www.stata.com/users/kcrow/tab2xl

in Stata.

Categories: Programming Tags: , , , ,
  • Leonardo Goes Shibata

    I wrote an alpha version of a program that can export two-way tabulations to excel.

    Please check at: https://github.com/leoshibata/tab2xl2

  • santhosh

    i have one doubt… is it possible to transfer stata 12 result into excel spreadsheet? i tried that but i cant….

  • Bryan

    Great job on tab2xl2. I made a couple of corrections to use it – you have an “A” in one of the putexcel commands that keeps the column labels in column A versus column setting, and I added the sheet option. Thanks for your work on this !!!

  • Leonardo Goes Shibata

    Thank you! Fixed that!

  • AU

    Hello,
    I am trying to use tab2xl to export frequency tables from stata to excel for multiple variables at once. do you have suggestions for a loop that can do this? I have tried

    local variables price mpg rep78 headroom trunk weight length turn displacement gear_ratio foreign

    local col=1

    foreach var of varlist `variables’ {

    tab2xl `var’ using “d:UsersautahDesktop1test.xls”, row(1) col(1) replace

    local col=`col’+6

    }
    ..but stata keeps replacing the last table with the next so in the end i end up with only 1 table in my Excel sheet.
    Any advice on how to code this so that I have all the tables for all the variables in the same sheet?
    Thanks!

  • Alicia

    Hi,
    I’m using putexcel to export a matrix I created storing a number of results. I have given names to the columns and rows of the matrix, but the putexcel command is only saving the content of the matrix (not the column and row names). If I list the matrix, it includes the row and column names, so they are specified correctly. I tried specifying rownames and colnames options, but it returns an error message (eg option colnames not allowed). I see from your description above that I can add the row and column names manually specifying each one (eg B1=(“Population”)) Is there any way I could export those row and column names automatically?

    Here is an example of the code that causes the error, using a simplified matrix:

    version 14

    foreach V of var ipd sero_7{
    matrix ci1_`V’=J(1,5,-99)
    matrix colnames ci1_`V’= Population IncidenceRate StdErr 95Lower 95Upper
    matrix rownames ci1_`V’=0-4_2009
    quietly ci `V’ if year==2009 & age_code_2==1, poisson exposure(population)
    matrix ci1_`V'[1,1]=r(N)
    matrix ci1_`V'[1,2]=r(mean)
    matrix ci1_`V'[1,3]=r(se)
    matrix ci1_`V'[1,4]=r(lb)
    matrix ci1_`V'[1,5]=r(ub)
    matrix list ci1_`V’
    putexcel set ipd_results, modify sheet(“`V'”,replace)
    putexcel B2 = matrix(ci1_`V’) , colnames
    }

    I’d appreciate your help!

  • Molly

    FYI, it’s probably the fact that I’m very new to Stata, but I was getting option matcol() not allowed
    r(198);
    when trying to use tab2xl2

  • Molly

    Found my own answer further down the page, string variables don’t work, so categorical variables need to be encoded. See from below:
    encode operator, generate(operator2)

  • Leonardo Goes Shibata

    Molly, sorry for taking long to answer! I’m glad that you could find a solution anyway. It’s always a good a idea to encode your categorical variables. Cheers!

  • houda

    I am Houda , I have the same problem of how to export results of the cros-correlations and the AR(1) coefficient, this is a part of my code
    **********************************
    clear*
    set more off
    cd “C:UsersHOUDADesktopNew thesisTMENAALGERIAallsample”
    log using “ALG.log”, replace
    use ALG_data.dta, clear
    gen time=_n
    tsset time
    rename var2 RO /*real GDP*/
    rename var3 NO /*nominal GDP*/
    rename var4 NGC /*NOMINAL GOVERNMENT CONSUMPTION EXPENDITURES*/
    rename var5 NPC /*nominal private consumption*/
    rename var6 RGC /*real GOVERNMENT CONSUMPTION EXPENDITURES*/
    rename var7 RPC /*real private consumption*/
    rename var8 M /*nominal imports*/
    rename var9 X /*nominal exports*/
    rename var10 TB /*net exports= X-M*/
    rename var11 RX /*Real exports*/
    rename var12 RM /*Real imports*/
    rename var13 RTB /*Real trade balance*/
    rename var14 RINV /*Real investment*/
    rename var15 Eng /*energy consumption*/
    rename var16 RWR /*Real workers’ remittances*/
    rename var17 EXD /*external debts*/
    gen tby=TB/NO
    gen rtby=RTB/RO
    gen lnRO=ln(RO)
    gen lnRGC=ln(RGC)
    gen lnRPC=ln(RPC)
    gen lnRINV=ln(RINV)
    gen lnEng=ln(Eng)
    gen lnRWR=ln(RWR)
    rename var1 Years
    foreach var of varlist lnRO lnRGC lnRPC lnRINV lnEng lnRWR rtby{

    twoway (line `var’ Years, sort),title (ALGERIA) subtitle (`var’) ytitle(“`var'”) xtitle(“Years”)
    graph save Graph “C:UsersHOUDADesktopNew thesisTMENAALGERIAallsamplepicoriginal_`var’.gph”, replace

    tsfilter hp hp`var’ = `var’,smooth(100) trend(hp`var’_tr)
    *********************Volatility, peristence and correlations of the cyclical component***********
    xcorr hplnRO hp`var’, lags(1) table
    egen hp`var’_sd = sd(100*hp`var’)
    gen hp`var’_relsd=hp`var’_sd/hplnRO_sd
    corrgram hp`var’, lags(1)
    egen hp`var’_tr_sd = sd(100*hp`var’_tr)
    gen hp`var’_tr_relsd=hp`var’_tr_sd/hplnRO_tr_sd /*the contribution of the trend volatility to the gdp volatility*/
    gen hp`var’_tr_relsdd=hplnRO_tr_sd/hp`var’_tr_sd /*the contribution of the trend volatility of GDP to the variables volatility-reverse of the ratio above*/
    }
    ************subperiods analysis****************
    gen period1=(time31)
    ************gross variables analysis***********************
    foreach var of varlist lnRO lnRGC lnRPC lnRINV lnEng lnRWR rtby { /*Identification of the variables cyclical component obtained from the first part */
    forvalues i = 1/2 { /*we keep the denomenation {`var’ for gross variables */
    gen `var’`i’=`var’ if period`i’==1

    }
    }

    foreach var of varlist lnRO lnRGC lnRPC lnRINV lnEng lnRWR rtby { /*growth rate computing for gross variables */
    forvalues i = 1/2 {

    ************growth series of gross variables********
    gen `var’`i’_ret=`var’`i'[_n-1] if period`i’==1
    gen GROWTH_`var’`i’= (`var’`i’-`var’`i’_ret) if period`i’==1
    egen GROWTH_`var’`i’_sd = sd(100*GROWTH_`var’`i’) if period`i’==1
    gen GROWTH_`var’`i’_relsd=GROWTH_`var’`i’_sd/GROWTH_lnRO`i’_sd if period`i’==1

    twoway (line `var’`i’ Years, sort),title (ALGERIA) subtitle (`var’`i’) ytitle(“`var’`i'”) xtitle(“Years”)
    graph save Graph “C:UsersHOUDADesktopNew thesisTMENAALGERIAallsamplepic`i’original_`var’`i’.gph”, replace
    }
    }

    save ALG.dta, replace
    log close

    ************************************************

    So how can I exports the results of xcorr and corrgram?, I need your help because I am working on 19 countries, and going back to the log file to copy results variable by variable take a lot of time.
    thanks

  • Anna Mejldal

    Hi, I am trying to use tab2xl and it works for some variables (0/1) but other times I just get “to few quotes” without having changed anything but the variable name (to another numeric one, though with 12 categories and som missing). Any idea what my mistake could be? Thanks!