Exporting Correlation Matrix to Excel

[Caroline Hoch]

I am having trouble exporting my correlation matrix to Excel. From what I could find online, I tried the following code, which returned "using not allowed." Help!

putexcel A1 = matrix(r(C), names) using "file_name.xlsx", replace sheet(RQ1.1, replace)

[Simon Pfaff]

Caroline, you could try something like this:

* excel
putexcel set "file_name.xlsx", sheet(title) modify

* data
use "D:\example\xyz.dta"

* correlation
corr V035 V033 V044

putexcel A1 = matrix(r(C)), names

[Caroline Hoch]

Thanks, Simon! I did the following:

putexcel set "file_name.xlsx", sheet(RQ1.1, replace) modify
pwcorr v1 v2 v3, sig star(.05)
putexcel A1 = matrix(r(C)), names

However, the p-values don't get exported. Do you know how to solve this?

[Andrew Musau]

The following uses estout from SSC, which you can export as a CSV file and open in Excel: https://www.statalist.org/forums/for...ts-of-a-pwcorr.

[Simon Pfaff]

Caroline, I mainly use putdocx, to create word documents. I didn't figure out a way to include the significance stars using putexcel.

So I first used collect layout to build a collection. I then exported it to excel. You need Stata 17 or higher.
I jut realized, that you could also use putexcel to save the collection to excel.

This is well documented in "STATA customizable tables and collected results reference manual" (example 1).

collect clear

* Data
use "D:\xyz.dta"

* correlation
pwcorr V033 V044 V050, sig star(0.5)

* building a collection
matrix define vech = vech(r(C))
collect get corr=vech(r(C)) sig=vech(r(sig))
collect layout (rowname#result) (roweq)
collect stars sig 0.01 "***" 0.05 "**" 0.1 "*", attach(corr) shownote
collect style cell result[corr], maximum(0.99, label("-")) nformat(%6.4f) halign(center)
collect layout (rowname#result[corr]) (roweq)

* a) export to excel
*collect export myfile.xlsx, replace

* b) putexcel
putexcel A1 = collect

There are a lot of possibilities for layouting the table.

Hope, it helps.
Simon.