st: Tabstat transpose and export to excel

["Vandekerckhove, Sem" <Sem.Vandekerckhove@hiva.kuleuven.be>]

If anybody, like me, has an SPSS head but Stata fingers, here's some syntax to transpose tabstat output and get it to Excel (an automatized "compare means").

The result is an series of means for different variables in the rows and categories of the by-variable in the columns. You need the svmat2 and svmatf plugins. I have not found a way to label the categories though.


cd "D:\Projecten\Eurofound QoW\Long Stata"
use "bemp_subdII.dta", clear
do 0macro
// return list

foreach byvar of varlist ///
age_cat skill_degree skill_cat y10_isco ///
y10_nace_r2_21 comp_size ///
y10_hh2a y10_q1 y10_q15a y10_q15b y10_q10 y10_q16 {
        cd "D:\Projecten\Eurofound QoW\Long Stata"
        use "bemp_subdII.dta", clear
        do 0macro
        cd "D:\Projecten\Eurofound QoW\Long Stata\Descriptives"
qui tab `byvar'
scalar catnr = r(r)
display catnr

local run 1
while `run' <= catnr {
cd "D:\Projecten\Eurofound QoW\Long Stata\Descriptives"
        qui     tabstat sp speed time pace repet ep ta wta tc tw ///
                ppl hazar com fix wtf ///
                contr ft carr train nwh earn ///
                haras man say voice, by(`byvar') s(mean) save
        matrix mat`run' = r(Stat`run')'
svmatf , mat(mat`run') fil(`byvar'_`run'.dta)
use `byvar'_`run'.dta, clear
        rename mean cate`run'
        sort row
save `byvar'_`run'.dta, replace
cd "D:\Projecten\Eurofound QoW\Long Stata"
use "bemp_subdII.dta", clear
local run = `run' + 1
}
*

cd "D:\Projecten\Eurofound QoW\Long Stata\Descriptives"
        matrix total = r(StatTotal)'
        svmatf , mat(total) fil(`byvar'_total.dta)
use `byvar'_total.dta, clear

local run 1
while `run' <= catnr {
di `run'
sort row
qui     merge 1:1 row using `byvar'_`run'.dta, keepusing(cate`run') update replace sorted nogenerate
erase `byvar'_`run'.dta
local run = `run'+1
}

save    `byvar'_total.dta, replace
use             `byvar'_total.dta, clear
outsheet using `byvar'_total.xls, replace
erase `byvar'_total.dta
cd "D:\Projecten\Eurofound QoW\Long Stata"
clear
}




Sem Vandekerckhove

wetenschappelijk medewerker
Onderzoeksgroep Arbeid & Organisatie

HIVA - Onderzoeksinstituut voor Arbeid en Samenleving
Parkstraat 47
3000 Leuven

sem.vandekerckhove@hiva.kuleuven.be
tel. 016 32 33 31

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/