Export table to excel

[Caroline Calogero]

Hi all,

I am trying to navigate how to export a table to excel. This code works correctly to create the tables I want and even correctly creates a new sheets with each manufacturer, but does not actually paste the table. I have tried this a few different ways. Any ideas?

[Caroline Calogero]

Also, I get a "using not allowed" error when I tried putexcel.... using "filename"

[Nick Cox]

I almost never use putexcel but I think I can answer #2. The point of putexcel set is to specify the destination file, so no other putexcel subcommand either needs or allows a using specification.

[Caroline Calogero]

Is there a better command than putexcel? To get table results into excel?

[Joseph Coveney]

This code works correctly to create the tables I want and even correctly creates a new sheets with each manufacturer, but does not actually paste the table.

You don't mention it, but you must be using an older version of Stata, because table hasn't allowed that syntax for the last two releases.

Regardless, I think that table doesn't store any results in an r(table) matrix and so that matrix will be empty when you try to export it to Excel with your code. That would explain why your code is producing the worksheets, but isn't pasting anything into them.

Is there a better command than putexcel? To get table results into excel?

You could try something like that below. Begin at the "Begin here" comment; the code above is just to produce an example dataset that I imagine resembles yours.

version 18.0 // <= substitute your version of Stata here

clear *

// seedem
set seed 1494640909

quietly set obs 2
generate byte mnf = _n

quietly expand 2
generate byte customer = _n

quietly expand 5
generate byte product = _n

generate double share = runiform()

*
* Begin here
*
preserve

set type double
collapse (mean) share, by(mnf customer product)

quietly levelsof mnf, local(mnfs)
foreach mnf of local mnfs {
    quietly export excel customer product share ///
        using "`c(pwd)'`c(dirsep)'Revenue Shares.xlsx" if mnf == `mnf', ///
            sheet(Manufacturer_`mnf') firstrow(variables)
}

restore // not actually necessary, but for the sake of completeness

exit

[Jeff Pitblado (StataCorp)]

If your Stata is older than Stata 17, then your best solution is given by Joseph in #5. Either way, if you choose to use collapse and export excel, then you will need some extra work to get the marginal means and arrange the sheets into two-way tables.

If you have access to Stata 17 or newer, then it is possible to publish the separate two-way tables, with marginal means, to Excel using the updated table command and the new collect suite.

* simulate some data
set seed 1494640909
quietly set obs 2
generate byte mnf = _n
quietly expand 2
bysort mnf: generate byte customer = _n
quietly expand 5
bysort mnf customer: generate byte product = _n
generate double share = runiform()

* build the tables -- the parens mean:
* rows indexed by customer,
* columns indexed by product,
* tables indexed by mnf,
table (customer) (product) (mnf), statistic(mean share)

* get the row and column specifications for arranging/exporting each
* table to a separate worksheet
collect layout
local rowspec = s(rows)
local colspec = s(columns)

* get the levels for the individual tables
collect levels mnf
local levels = s(levels)

* start with a new file
local modify replace
foreach l of local levels {
    * arrange a specific table for export
    collect layout (`rowspec') (`colspec') (mnf[`l'])
    * the "Total" level is coded as ".m"
    if missing(`l') {
        local sheet Manufacturer_Total
    }
    else    local sheet Manufacturer_`l'
    * export to a specific sheet
    collect export "Revenue Shares.xlsx", sheet(`sheet') `modify'
    * update the file after exporting the first sheet
    local modify modify
}

Here is a screen-shot of the Numbers application on my Mac.