New on SSC: xtable - module for exporting table output to Excel

[Weverthon Machado]

Dear all,

Thanks to Kit Baum, my command xtable is now available on SSC. You call install it by typing ssc install xtable.

xtable exports output from Stata's built-in command table to an Excel spreadsheet. It works as drop-in replacement: you can just replace table with xtable in your code and run it the same way. You will get the exact same output in the results window, plus a link to an Excel spreadsheet containing the exported tabulation. The only real restriction is that you are not allowed to use the by prefix.

table is a very powerful and flexible command, but it's not easy to get its nice tables out of Stata for further processing. The putexcel command introduced in Stata 13 made exporting stuff to Excel a lot easier, but it relies on stored results and table produces none. That's what xtable does: leveraging table's replace option, it creates a matrix that reproduces as best as possible what's shown on screen and then exports it using putexcel. Because it depends on putexcel, xtable requires Stata 13.1 or newer.

Basic syntax

You can use the exact same syntax from table, because xtable will just pass the arguments to table and then export the results. So, you can run:

sysuse auto
xtable foreign rep78, c(mean mpg sd mpg)

The only difference you will see is a link to the Excel spreadsheet containing the results:

Exporting options

By default, xtable will export the tabulation to a file named "xtable.xlsx" in the current working directory, overwriting it if it already exists. You can fine tune this with options that will be passed to putexcel. For example, the following code saves the table in the "prevalence" worksheet of the "results.xlsx" file, modifying the file if it already exists:

webuse byssin
xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) sc filename(results) sheet(prevalence) modify

Finally, the noput option will keep xtable from writing to any file. Instead, it will just store the matrix in r(xtable), as it does anyway. You can then include it in a putexcel call:

webuse byssin
xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) noput
putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names) using myfile.xlsx, replace

This might be particularly useful if you use Stata 14 or newer, which added formatting options to putexcel.

----

Please see help xtable for further information.

Feedback is welcomed. I'll be happy to address any questions or suggestions here or by email ([email protected]).

Regards

[Weverthon Machado]

xtable has been updated on ssc. The latest version fixes two errors:

- "invalid syntax" when using string variables (reported here).
- "invalid file specification" when using four superrow variables (the by() option).

[Ribka Berhanu]

Hi Weverthon Machado - thank you for creating xtable. I find it extremely useful.

Is there a way to specify the column and row in the excel output?

Thanks!

[Weverthon Machado]

Hi Ribka Berhanu. You can do this by using the "noput" option of -xtable- and then exporting the stored matrix "r(xtable)" with -putexcel-. Doing this you can fine tune a lot of stuff using putexcel's options, including formatting (for Stata >= 14). So, in this example we put a title in the A1 cell of the spreadsheet, then we skip a line and the table starts at the A3 cell:

webuse byssin
xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) noput
putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names) using myfile.xlsx, replace

[Hilary Shepherd]

Hi there. I am using xtable to export data in a table to excel which is really useful. I am using the option "column" which specifies that a column be added to the table reflecting the total across columns. The default is to add the resulting total to a new column at the end of the table. However I wish to place the values from "column" in the first column of my table. Is it possible to rearrange rows/columns in this way? Thank you.

[Weverthon Machado]

Hi Hilary Shepherd. This is unfortunately not possible, because the command tries to reproduce as close as possible the table shown on screen. More flexibility to depart from the layouts produced by the original -table- command would require a good amount of rewriting of -xtable-.

[Harlan Sayles]

Hi Weverthon,

I'm currently using STATA SE version 17.0. When I run the code snippet you've posted, I get the following error.

CODE:
webuse byssin
xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) noput
putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names) using myfile.xlsx, replace

RESULT:
. putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names) using myfile.xlsx, replace
using not allowed
r(101);

If I first run the putexcel set command as below and then try to run the third line of your code, I get the following error. I've also included my matrix so you can see what it looks like and if it is somehow different from yours. Any idea why I'm seeing this "conformability error"?

. putexcel set myfile.xlsx

. putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names)
conformability error
r(503);

. matrix list r(xtable)

r(xtable)[3,4]
No Yes No Yes
Least .01075269 .01015228 .00815494 .01627742
Less .02 .00816326 .0136612 .01431493
Most .08208955 .16791046 .08333334 .22950821

[Weverthon Machado]

Hi Harlan Sayles,

I used the older syntax for -putexcel- and haven't updated the help files. Without the "noput" option, the internal call to -putexcel- still works because the ado file is versioned. So this works for me on Stata 16:

webuse byssin
xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) noput
putexcel set myfile.xlsx
putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable)), names

(besides calling "putexcel set" instead of "using", note that the "names" argument also changes)

[Maxence Morlet]

Hi Weverthon,
Sorry for revisiting this thread, but I have a question concerning your command that is quite related to this thread:

My code is:

cd c:/results

local  y_vars "appquality finalgrade_school"
foreach y in `y_vars'{
xtable `y', c(mean `y') format(%9.3f) noput
}
putexcel set myfile.xlsx
putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable)), names

I keep on getting invalid format; I have tried all sorts of formats and keep on getting the same error. Here is my data:

input byte(appquality finalgrade_school)
. .
. .
. .
. .

Please could you help me?

I should specify: I did try a destring command, still got invalid format %10.0g errors, and when I tried specifying an alternative format it also gave me invalid format errors

[Weverthon Machado]

Hi Maxence Morlet ,

I can't reproduce your error and you only provided missing data. But a few observations:

- As stated in the help file, the "format" argument is inconsequential for the exported spreadsheet, so you can simply drop it.
- Using a variable (`y' in your loop) both as rowvar and inside the contents() argument is invalid syntax for -table- and, thus, for -xtable-. Can you clarify what kind of table you are trying to make?
- If you don't want to export only the results of the last call to -xtable- in the loop, you should call -putexcel- inside the loop.

This minimal example, modified from your code, might help as a starting point:

sysuse auto

local  y_vars "foreign headroom"
foreach y in `y_vars'{
    xtable `y', noput
    putexcel set `y'.xlsx
    putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable)), names
}

[Maxence Morlet]

Dear Weverthon Machado,

Thanks for your response! Basically I would like to create a table (omitting the title and just the raw data) a bit like this (using variable names from sysuse auto) and export this table to excel:

headroom	24
mpg	42

Where the second column is the arithmetic mean of the variable.

Providing a little more information on my data:

 input byte(appquality finalgrade_school)
0 .
. 4
2 .

How could I adapt the baseline code you provided to perform this operation?

Apologies for being vague and unclear in the previous post, here is my problem with the code I tried:

local  y_vars "appquality finalgrade_school"
foreach y in `y_vars'{
    xtable `y', c(mean `y') noput
    putexcel set `y'.xlsx
    putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable)), names
}
invalid format %10.0g

Thank you for explaining the invalidity of inserting the y macro in the loop and well as in the c() argument. How could I then specify in the code that I would like the variable names of `y´ to figure in the first column, and the respective means in the second column?

[Weverthon Machado]

Maxence Morlet

I think that -table-/-xtable- might not be the best options for what you are looking for, especially if you want the means of many variables. But a workaround like this should do the trick for a simple case:

sysuse auto
gen row = " "
xtable row, c(mean headroom mean mpg)

Note that you don't need the loop (nor the separate call to -putexcel- if you don't need customization).

[Sergio Goldbaum]

Hi Weverthon Machado

I am running:

xtable (v6121) [fweight = v0010m], statistic(mean v6531) statistic(sd v6531) statistic(sumw) statistic(frequency)

and the result is:

option statistic() not allowed

it worked perfectly with command table.

v6121 is the variable for religion in the Brazilian Census 2010, and v6531 is the household income.

does not xtable work with "statistic"? do you have another suggestion?

TIA

PS. I guess I solved it. Just used c instead of statistic. But it didn't work with sumw and frequency. It seems there is a gap between command xtable and some updated command table.

[Weverthon Machado]

Hi Sergio Goldbaum,

You are right, it's a gap between -xtable- and the updated -table-: the statistic() option was added to the latter in Stata 17, while the former was written with a previous version. I don't have access to Stata 17, but from the documentation it seems that the revamped -table- even has export options that might render -xtable- obsolete.