Export xtsum with putexcel?

[Rachel Sleeps]

Hi,

just like someone else, I have trouble exporting the results of xtsum (see link below). However, I am trying to export to excel and not LaTeX.

I am using Stata 13 and I tried to export the results of xtsum via putexcel. I created a local for my covariates and used the following code:

xtsum `all'
    putexcel A1=matrix(r(table), names) using "Test.xls", sheet("xtsum", replace)

However, it does not work. It only gives me a "matrix" in Excel with two cells (A2="r1", B1="c1"). Is there any quick and elegant way to get the entire xtsum output to excel?

Thanks for your help!
/R

[Roberto Ferrer]

When using a _varlist_, -xtsum- will only store the results for the last variable. If you want to export results for several variables then you might consider running -xtsum- and -putexcel- within a loop, one variable at a time. The other issue I see is that -xtsum- doesn't save anything in r(table). Exporting the saved results of -xtsum- with one variable can be examplified with:

webuse nlswork, clear

xtsum birth_yr
putexcel A1=rscalarnames B1=rscalars using myfile, sheet("Results")

It's likely you're trying to extrapolate the example from the -help- file that uses -regress- and not -xtsum-. Recall that each command saves its own results, and you must use -putexcel- accordingly.

[Rachel Sleeps]

Thanks! I almost made it work. I adapted your code to the following:

foreach a of local all {
    local i = 1 + `i'
    xtsum `a'
    putexcel B`i'=rscalars using "Persistence_results.xls", sheet("sums") colwise  modify
    }

With adding the local "i" and adding the option "colwise" I managend to export the xtsum output per variable in local "all" shifting one column to the right each time the loop runs through. Yet, I did not manage to add the variable names and legend. I tried to check the stored results, but it did not work out so far. Any ideas?

Thanks!
/R

[Roberto Ferrer]

Defining -local i- like you've done can give undesired results if you have defined -local i- somewhere before your loop. Ideally, you would make sure it starts out with some number (i.e. initialize it), and then increment:

local i = 0
foreach ... {
    local i = `i' + 1
    ...
}

Regarding scalar titles (what you refer to "legend", I think), -help putexcel- states:

resultset is a shortcut name used to identify a group of return values that are stored by a Stata command. resultset can be

rscalars rscalarnames
escalars escalarnames
emacros emacronames
rmacros rmacronames
ematrices ematrixnames
rmatrices rmatrixnames
e* enames
r* rnames

Variable names can be used by simply refering to -local a- : `a'.

Finally, -foreach a of local all ... - will work, but it's better to use -varlist-, only because what you are dealing with are variables. Example:

sysuse auto

local myvars mpg weight
foreach v of varlist `myvars' {
    display "`v'"
}

[Rachel Sleeps]

Thank you very much! It is working now!

It is probably still not the most elegant way to solve the issue, but I did it the following way with yout help :

xtsum `all'
qui putexcel B2=rscalarnames using "results.xls", sheet("sums") modify colwise

local i = 2    

foreach a of varlist `all' {
    local i = 1 + `i'
    xtsum `a'
    putexcel A`i'=("`a'") B`i'=rscalars using "results.xls", sheet("sums") colwise   modify
    }

That is I first run xtsum and export rscalarnames to B2. I than set local = 2 and loop xtsum foreach variable in varlist `all'. I export the variable names to the column A and rscalars to B and following.

[Attaullah Shah]

For those interested, asdocx can now export the output from xtsum command to Word, Excel or LaTeX. Here is an example:

webuse abdata.dta, clear
asdocx xtsum emp k wage cap , replace

or send the output to Excel

asdocx xtsum emp k wage cap , replace save(Myfile.xlsx)