Export Tabulate Table to Excel

[Lea Birm]

Hi all,
I am currently trying to export a tabulate table for several observations to an excel sheet each.

putexcel set "FilePath.xlsx", sheet(Descriptive)

for each fruit in "Apple" "Pea" "Grape" { 
preserve 

keep if vegetable == "`fruit'"
tab Apple
putexcel A1=("`fruit") B1=("Freq.") C1=("Percent") using "FilePath.xlsx", replace
putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N)) using "FilePath.xlsx", modify

restore 
}

However, its not giving me an excel file and says that the options are not valid.
Do you gave any idea on how I can adjust the code so that it works?

Best,
Lea

[George Ford]

you don't need using/replace/modify. that goes with putexcel set.

I also don't think the putexcel stuff is correct. look at the examples in the help file.

and, I don't think tab is going to work, because it does not store the percentages.

you can get the raw numbers using , matcell(C) matrow(R) and then do the computations.

tabstat may be a better option.

[George Ford]

I don't use table, but it might work.

You can try something like this, and then export the entire matrix T to excel.

clear all
sysuse auto, clear

capture program drop tabit
program tabit , eclass
syntax varlist(max=1 numeric) [if]
tokenize `varlist'
local var = "`1'"
qui tab `var' `if', matcell(F) matrow(R)
matrix P = F/`r(N)'
matrix C = P 
local r = rowsof(F)
forv i = 1/`r' {
    if `i' > 1 {
        matrix C[`i',1] = C[`i',1] + C[`i'-1,1]
    }
}
forv i = 1/`r' {
    local rp = round(P[`i',1],0.0001)*100
    local rc = round(C[`i',1],0.0001)*100
    matrix P[`i',1] = `rp'
    matrix C[`i',1] = `rc'
}
matrix T = R , F , P , C
matrix colnames T = Row Freq. Percent Cum.
matrix T = T \ `r(r)' , `r(N)' , . , .
end


putexcel set "S:\STATA\FilePath.xlsx" , replace
local c = 1
forv i = 0/1 {
    tabit rep78 if foreign==`i'
    putexcel A`c' = "foreign == `i'"
    local c = `c'+1
    putexcel A`c'=matrix(T) , colnames
    local c = `c' + rowsof(T) + 2
}