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.

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.

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 Arow'=("val_lab'") Brow'=(freq_val') Crow'=(percent_val') ///
Drow'=(cum_percent') using results, modify
local row = row' + 1
}

putexcel Arow'=("Total") Brow'=(r(N)) Crow'=(100.00) using results, modify


The above commands produce this table in Excel:

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.

• 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
}

• 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
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 hpvar’ = var’,smooth(100) trend(hpvar’_tr)
*********************Volatility, peristence and correlations of the cyclical component***********
xcorr hplnRO hpvar’, lags(1) table
egen hpvar’_sd = sd(100*hpvar’)
gen hpvar’_relsd=hpvar’_sd/hplnRO_sd
corrgram hpvar’, lags(1)
egen hpvar’_tr_sd = sd(100*hpvar’_tr)
gen hpvar’_tr_relsd=hpvar’_tr_sd/hplnRO_tr_sd /*the contribution of the trend volatility to the gdp volatility*/
gen hpvar’_tr_relsdd=hplnRO_tr_sd/hpvar’_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 periodi’==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 periodi’==1
gen GROWTH_var’i’= (var’i’-var’i’_ret) if periodi’==1
egen GROWTH_var’i’_sd = sd(100*GROWTH_var’i’) if periodi’==1
gen GROWTH_var’i’_relsd=GROWTH_var’i’_sd/GROWTH_lnROi’_sd if periodi’==1

twoway (line var’i’ Years, sort),title (ALGERIA) subtitle (var’i’) ytitle(“var’i'”) xtitle(“Years”)
graph save Graph “C:UsersHOUDADesktopNew thesisTMENAALGERIAallsamplepici’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