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:
• Michael Smith

This is exactly what I needed today! …and then I remembered I have stata 12, and was sad.

• Harrison Startz

It’d be awesome to be able to format Excel tables from within Stata, for example, make the first row all bold.

I second Harrison’s request. I had been hoping to use -putexcel- with the modify option to fill values into nicely formatted Excel table shells. Unfortunately, -putexcel- ignores (overwrites) the existing formatting in the Excel spreadsheet, changing the font to a default font, removing any Excel number formatting such as percentage and number of decimal places, etc. Would it be possible to add an option to -putexcel- that would keep any existing Excel formatting in place (i.e., an option that would make -putexcel- work like the Paste Special -> Values command in Excel)?

• Kevin Crow

We will look at adding both of the below features to putexcel in a future
update or version of Stata.

1) Retain a cell’s format after writing data to it
2) Cell formatting

Excellent! That would be most welcome.

• Ana Abreu

Hi Kelvin,

I need to export results from a survey estimation to excel but I have several rows to export. Therefore. I would need a loop to export all the values in a row wise manner. I have several regions and for each one i would need this stratification.

svy, sub(x’): proportion curr_smk if entidad==”25″ & area==2
svy, sub(x’): proportion curr_smk if entidad==”25″ & area==1
svy, sub(x’): proportion curr_smk if entidad==”25″ & area==2 &sexo==2
svy, sub(x’): proportion curr_smk if entidad==”25″ & area==2 &sexo==1
svy, sub(x’): proportion curr_smk if entidad==”25″ & area==1 &sexo==2
svy, sub(x’): proportion curr_smk if entidad==”25″ & area==1 &sexo==1
}

There is a way to have a loop within putexcel command?

• Karola

Hi I know this is not related to the post, however would anyone know how to calculate the boone indicator for competition in stata?

• Ricardo

Is there a way to add brackets or parenthesis when exporting standard errors via putexcel?

• Christian

hi kevin, i am not familiar with STATA, but I have to gain some tablulated data throught remote commands.
Q: how can i generate XLS from the TABSTAT command?

• Kevin Crow

You can use putexcel in your loop, but you will probably want to use the advanced syntax. Your do-file would look something like

putexcel set myfile.xlsx, modify sheet(“mysheet”)
local row = 1
svy, sub(x’) …
putexcel Arow’ …
local row = row’ + 1
svy, sub(x’)…
putexcel Arow’ …
local row = row’ + 1
}

• Kevin Crow

I don’t believe there is a command to do this in Stata, but you might try emailing tech-support@stata.com to make sure that there is not.

• Kevin Crow

Yes you can. You will have to do a little programming to recreate the entire table, but you can get the numbers generated in tabstat with the save option.

. tabstat mpg for, save
stats | mpg foreign
———+——————–
mean | 21.2973 .2972973
——————————

. return list
matrices:
r(StatTotal) : 1 x 2

. matrix list r(StatTotal)
r(StatTotal)[1,2]
mpg foreign
mean 21.297297 .2972973

• Kevin Crow

You can use the : display extended macro function. For example,

. clear
. qui sysuse auto, clear
. qui regress for mpg weight

. local se : display “[” _se[mpg] “]”

. di “se'”
[.01267011]

See help extended_fcn for more help.

• Kevin Crow

Where are you saving the file corr.xlsx? Make sure you have permission to save to the directory in question.

• Jeef

Using putexcel in a loop, I am trying to add data across columns. I understand how to post data down rows. My syntax was:
local row 5
foreach var of varlist *_high{
sum var’
putexcel set “column.xls”, sheet(“summary”)modify
local high = r(sum)
local high : display %9.0f high’
putexcel Hrow’=(high’)
local row=row’ +1
}

How can I have the data put into A1 B1 C1 etc
Thanks

• Kevin

You can use the undocumented mata function numtobase26() to get the Excel column names. For example,
 . local i = 1

 . mata:st_local("col_num", numtobase26(strtoreal(st_local("i")))) . di "col_num'" A 

• Rob

Hi Kevin! Thanks for bringing putexcel to our attention; I have often wondered if a command like this is available. Thanks even more for writing tab2xl – this addresses my needs even more specifically, and will save me lots of time doing tedious MS-word-based parsing of tabular output.

However, I can’t seem to figure out the right syntax for the tab2xl command. I’m doing (what I think is) a fairly simple
tab2xl operator using temp_june10, col(2) row(2) replace

and I get the error message

option matrow() not allowed

… do you have any advice to offer? (In the future, btw, I’d love to be able to use if and by() options in the tab2xl syntax, but for now I can just keep/drop within a preserve/restore wrap and get my if and by() done that way…

• ds

If the field you are tabulating, “operator” is a string variable then you will need to encode the variable before running the command.

Ex:
encode operator, generate(operator2)
tab2xl operator2, using temp_june10, col(2) row(2) replace

• Kevin Crow

Rob, ds is correct. -tab2xl- does not work with string variables.

• Chas

• joco24

Hello Kevin, I would like to know how I can convert the summary output of a regression into a graph. This is the information that I have.
Linear correlation coefficient r = 0.794556
Coefficient of determination = 0.631319
Standard error of estimate = 12.9668
Explained variation = 5182.41
Unexplained variation = 3026.49
Total variation = 8208.90
Equation of regression line y1 = 0.725983X + 16.5523
Level of significance = 0.1
Test statistic = 0.794556
Critical value = 0.378419
I am stuck and need some help, Thnx.

• Filipe

I get this error every once in a while. I am running a simulation and in some iterations the file is saved, but at some point it stops with the same error as above.

• Kevin

Can you email into tech-support@stata.com. Your graph question can be answer there.

• Kevin

Sometimes your anti-virus software prevents writing to a file quickly because it is inspecting it with each pass of the loop. You can turn off your anti-virus software while running your program or you can use the sleep command to slow down the loop to get around the problem.

• mfarris

Hi Kevin, I was wondering if you could help me out. I would like to use putexcel to export the results of my errors, some are loop commands. For example here are 2 of the commands I would like to export the results:

list UID StudyID DateCompleted if !inrange(dofc(DateCompleted), td(1apr2012), td(2feb2015))

foreach var in GHQ_A3a_VigAct GHQ_A3b_ModAct GHQ_A3c_LiftGroceries GHQ_A3d_SevFlights GHQ_A3e_OneFlight GHQ_A3f_BendKneel GHQ_A3g_WalkMiles GHQ_A3h_WalkBlocks GHQ_A3i_WalkoneBlock GHQ_A3j_Bath {
list UID StudyID var’ if !inrange(var’, 1, 3)
}

• Filipe

Thanks!!

• http://www.thesagenext.com juliya mark

I really thankful to you and your blog. Here I got exclusive information. Thanks Kevin

• zhenya

does anyone know how to export tabstat data using putexcel?

• Emil Begtrup-Bright

When looping with this wonderful tab2xl ado, I’ve encountered this bug a couple of times even though the variables was numeric. After a lot of trial and error, two things seems to resolve the issue:

1) labels. When I applied labels, and then ran tab2xl, everything worked fine.

2) used: “order var1 var2 var3, last” on the variables so I made sure they were last in the data and in the order that the loop specified.

I think maybe 1) only worked because it actually just placed the variables in order in the end of the dataset, so 2) might be the only thing needed.

• Angela María Jiménez

Very nice
explanation, Chuck Huber! We have a question we would appreciate one of the
bloggers or you can help us with. Ware running xtmepoisson with a random effect
between a 1st level and a 2nd level variable. We believe the marginal effect of
the 1st level variable is the sum of the coefficient of this variable and the
interaction. But the interaction for nonlinear models is the cross derivative
of the dependent variable with respect to the 1st and the 2nd level variables
(See Norton, Wang and Ai (2004) Computing interaction effects and standard
errors in logit and probit models (Stata Journal).

How can we calculate the marginal effect of the 1st level variable, taking into
account the interaction effect?

• Angela María Jiménez

Very nice explanation, Chuck Huber! We have a question we would appreciate one of the bloggers or you can help us with. We are running xtmepoisson with a random effect between a 1st level and a 2nd level variable. We understand the marginal effect of the 1st level variable is the sum of the coefficient of this variable
and the interaction. But the interaction for nonlinear models is the cross
derivative of the dependent variable with respect to the 1st and the 2nd level
variables (See Norton, Wang and Ai (2004) Computing interaction effects and
standard errors in logit and probit models (Stata Journal).

How can we calculate the marginal effect of the 1st level variable, taking into
account the interaction effect?

• bubblezinca

Tab2xl is terrific. It would be great to be able to use it with two-way tables. Has anyone written code to do this (i.e., tab2xl var1 var2 using filename col(x) row(x)?

• Conner

Is there a way to format a matrix before writing it to an Excel file using putexcel? For example, suppose I want all the cells of a matrix to be in format %9.2fc. I know you can use format to alter how a matrix is displayed when using matlist, but I can’t tell if it is possible to format the matrix itself.
I ask because I am trying to write a matrix of summary stats to Excel, pre-formatted, and some of the numbers have commas in them. If I format the summary stats as local macros using the display extended macro function, and then put the local macros into a matrix, only the digits before the comma show up in the matrix. If I could format the matrix itself, this would solve the problem.

• Kevin Crow

Can you email into tech-support@stata.com with your dataset and code so that I can give you an accurate answer.

• Kevin Crow

Can you show me an example of what you need to export from tabstat? You can use the auto dataset to demonstrate an example.

• Kevin Crow

Not yet. This is something I will work on. Currently, a twoway tabulate does not store everything I need to export the table correctly.

• Kevin Crow

In Stata 14 putexcel allows you to change the numeric format of the data written.

http://www.stata.com/stata14/excel-cell-formatting/

• Lenny Lesser

Is there a way to run this with a “by” option? I need to run tabstat by 12 categories.

• Kavao Kamili

is there a way i can use unstack option with putexcel so that the result are posted on different row for different categories of a variable (eg. posting the column percentage by gender in separated rows)

• Kevin Crow

You can use if with tabstat to get the results you want.

sysuse auto, clear
by for: tabstat mpg
tabstat mpg if for == 0, save
matrix a = r(StatTotal)
putexcel A1=(a) …
tabstat mpg if for == 1, save
matrix a = r(StatTotal) …
putexcel A4=(a)

• Kevin Crow

There is not an unstack option, but you can transpose a matrix before using putexcel.

. sysuse auto, clear
(1978 Automobile Data)

. tab for, matcell(freq)

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 b = freq’

. matrix list b

b[1,2]
r1 r2
c1 52 22

You can the use formula() option to generate column percentages in Excel based on the written data.

• Mansoor

Hi Kevin, I am using STATA 11. Is there any way to incorporate the putexcel functionality into STATA 11. Or some code that will achieve the equivalent? I am very new to using STATA so please ‘dumb it down’ for me :). Thanks

• Lenny Lesser

Any update on this? I need to do export a tab2 table with row percentages, but I want the percentages next to the frequencies in each cell: “Row 1: 2(3%), 40(50%), etc…

• prakashmalin

Hello Kavin…

i am using stata12 & i used ‘putexcel’ & tabstat but not able to export data into excel… is there any such video in youtube so i can check?