Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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:
    Code:
    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:
    Click image for larger version

Name:	output.png
Views:	1
Size:	7.4 KB
ID:	1503329



    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:
    Code:
    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:
    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
    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 (weverthonmachado@gmail.com).

    Regards
    Weverthon Machado
    IESP/UERJ
    weverthonmachado.github.io
Working...
X