exporting pwcorr matrix....I'm confused!

[Ngozi ADELEYE]

Dear all, I want to export 'pwcorr' to excel and word, and needless to say I've tried every code (that I know) relating to exporting tables and having read some online tutorials none seem to work for 'pwcorr' except the 'correlate command'.

I tried using estpost, outreg2, esttab, tabout......I'm so confused as to which one is applicable to pwcorr. I also noticed that with 'pwcorr', Stata stores only the last 2x2 matrix, so I can't even use the 'putexcel' command.

Kindly assist me the right code. These are my variables:
lngini lnhom lngdppc lngdpgr xdcredit xtrade xrents rol corrupt dtpen xcons_exp xw_age xage1564 xpry_educ xsec_educ xter_educ xune_m xurbp

Thanks in anticipation.
Ngozi

[Clyde Schechter]

To the best of my knowledge, none of the usual commands for exporting estimates to excel or text files will work after pwcorr. pwcorr, as you already noted, does not return the correlation matrix, only the last correlation. Statistically that probably makes a lot of sense. In the presence of missing data, the apparent matrix of pairwise correlations is not the actual correlation matrix of anything, and may even be negative definite! So a lot of caution is required in using it, and making it hard to use is probably wise.

On the other hand, sometimes you need to present a table of those correlations for people to see. The solution I use for this is typically somewhat roundabout, and if somebody knows a simpler solution, I'd love to see it. But here's what I do:

local vbles lngini lnhom lngdppc lngdpgr xdcredit xtrade xrents rol corrupt dtpen xcons_exp xw_age xage1564 xpry_educ xsec_educ xter_educ xune_m xurbp

local vbles: list sort vbles
capture postutil clear
tempfile corrs
postfile handle str32 var1 str32 var2 float r_ using `corrs' local nvars: word count `vbles'
forvalues i = 1/`nvars' {
     local v1: word `i' of `vbles'
     forvalues j = `=`i'+1'/`nvars' {
          local v2: word `j' of `vbles'
          corr `v1' `v2'
          post handle  ("`v1'") ("`v2'") (`r(rho)')
     }
}
postclose handle
//  AT THIS POINT THE POST FILE CONTAINS ALL OF THE OFF-DIAGONAL CORRELATIONS, BUT IN LONG FORMAT
//  MAY WANT TO PRESERVE EXISTING DATA BEFORE PROCEDING
use `corrs', clear
reshape wide r_, i(var1) j(var2) string
rename r_* *  // REQUIRES A MODERN STATA THAT HAS GROUP RENAMING
order _all, alphabetic
order var1, first
sort var1
//  THE DATA IN MEMORY IS NOW THE UPPER DIAGONAL CORRELATION MATRIX
// FEEL FREE TO -export excel- or -export delimited- or -save-, or whatever.

[Clyde Schechter]

It just hit me, that there may be a simpler way. If you are absolutely sure that each of these variables takes on at least 10 distinct values, you can use

-polychoric [varlist], pw-

and then retrieve the matrix r(R) for use with -putexcel-. That's because polychoric does Pearson correlations between any variables that have at least 10 distinct values. But if any of your variables has fewer than 10 distinct values, you will get tetrachohric or polyserial correlations, as the case may be, instead of Pearsons.

[Richard Williams]

SSC (or possibly other sites) has some commands that might be useful, e.g. corrtab, corrtex, mkcorr. The help for the latter says "mkcorr produces a correlation table in a format that is easy to import into a spreadsheet or word processing document." Use -findit- . I haven't tried any of these so I can't vouch for them.

[ben earnhart]

Richard and Clyde have great suggestions. But, given the size of your matrix and assuming (I might be wrong) that this is a one-off or occasional issue, simply store the log file as .log (not .smcl). Then you can easily read it into Excel or the equivalent as a fixed-width file, You may need to do a single copy-and-paste to get the last few variables into the matrix. Voila -- done. I like automating everything, but so long as the original data is intact and the steps are easy to repeat, this would be by far the most efficient.

If you routinely run a log, turn it off, turn a new one on right before the pwcorr command, then don't forget to close. One quirk -- when looking for text files, I don't think they show up in Excel. So either look for all files, or deliberately rename your .log to .txt.

[ben earnhart]

ps. I used to use Stata to generate correlation matrices, then slice-and-dice before bringing into Lisrel after processing in Excel. So the process is intuitive to me but it's straightforward enough (start import at line 14 or whatever and it's pretty good at choosing columns) that I think you'll find it a piece of cake.

[Ngozi ADELEYE]

wow!.......thanks a great deal Clyde for taking the time to generate a do-file....I'll never I've come up with that in a million years!.........I'm totally indebted!.....God bless you. .....thanks Richard and Ben ....I'll try all the suggestions 'cos I do need the PW Corr matrix.

I'll give a feedback on how far I'm able to go.....and pls tell the Stata 'guys' to come up with only EASY and SIMPLE codes......

[Nathan E. Fosse]

Dear, all -
I'll make mention of this as it is something that I wanted to accomplish as well. Fortunately, Adrian Mander has a nice ado through SSC you may install. Here's the weblink:

http://econpapers.repec.org/software...de/s456713.htm

I hope this helps!

Regards,
- Nate

[Nathan E. Fosse]

Dear, all -
I'll make mention of this as it is something that I wanted to accomplish as well. Fortunately, Adrian Mander has a nice ado through SSC you may install. The weblink is attached, or just type:
. ssc install matpwcorr
. ssc install plotmatrix



I hope this helps!

Regards,
- Nate

[salma ktat]

wow!.......thanks a great deal Clyde for taking the time to generate a do-file....I'll never I've come up with that in a million years!.........I'm totally indebted!.....God bless you. .....thanks Richard and Ben ....I'll try all the suggestions 'cos I do need the PW Corr matrix.

I'll give a feedback on how far I'm able to go.....and pls tell the Stata 'guys' to come up with only EASY and SIMPLE codes......

Dear Ngozi,
I'm just having the same problem as you. I don't know if you finally find out how to import the correlation matrix into word or excel. I was trying different methods but none is working proporly.
Thanks in advance.

[ben earnhart]

Here is yet another approach. Actually, spreadsheet software is pretty good about importing correlation matrices, but it's a bit harder to explain exactly what to click on than to write Stata code:

*============MAKE FAKE DATA
clear
cd c:\data
set obs 1000
set seed 1971
gen x=rnormal()
forvalues i=1/20 {
gen x`i'=rnormal()*3+x
}

*============NOTE: MUST HAVE ".log" FILE ENDING TO GET RAW TEXT
log using tempcorr.log, replace
pwcorr x*
log close

clear
*============READ IN CORRELATION MATRIX.  MAY NEED TO MODIFY SLIGHTLY DEPENDING ON HOW WIDE 
*============IT IS IN THE LOG FILE
infix 9 first str13 v1 1-13 str6 v2 18-24 str6 v3 27-33 str6 v4 ///
      36-42 str6 v5 45-51 str6 v6 54-60 str6 v7 63-69 str6 v8 72-78  using tempcorr.log 

*=========FINALLY, EXPORT TO EXCEL
export excel using "mycorr", replace

[Tao Han]

I just found another way to do it...

ssc install logout
local mylist "lngini lnhom lngdppc lngdpgr xdcredit xtrade xrents rol corrupt dtpen xcons_exp xw_age xage1564 xpry_educ xsec_educ xter_educ xune_m xurbp"
logout, save("path + name") excel replace: pwcorr `list', star(.05)

You can open the xml file using excel and save it as excel

[Dirk Rottgers]

Great addition, Tao Han! The program is a wonderful help.

Two small things though: `list' should really be `mylist' and the resulting excel file shifts the horizontal list of variables to the left by one.

[Ngozi ADELEYE]

7 May 2015, 16:56
I just found another way to do it...

ssc install logout
local mylist "lngini lnhom lngdppc lngdpgr xdcredit xtrade xrents rol corrupt dtpen xcons_exp xw_age xage1564 xpry_educ xsec_educ xter_educ xune_m xurbp"
logout, save("path + name") excel replace: pwcorr `list', star(.05)

You can open the xml file using excel and save it as excel


Hi Tao and Dirk,
Still on exporting pwcorr matrix to excel sheet. I have installed logout and imputed this code as suggested:

local mylist "gini dcps rir gdppcgr gdppc inf fsd liq" logout, save("C:\Users\ng\Desktop\BCII.xml") excel replace: pwcorr `mylist', star(.05)....but got this error message:

invalid syntax
r(198);

Perhaps, I got something wrong, somehow...kindly assist, thanks!

[Ngozi ADELEYE]

Dear, all -
I'll make mention of this as it is something that I wanted to accomplish as well. Fortunately, Adrian Mander has a nice ado through SSC you may install. The weblink is attached, or just type:
. ssc install matpwcorr
. ssc install plotmatrix



I hope this helps!

Regards,
- Nate

Hi Nathan, I've installed both matpwcorr and plotmatrix, but I'm still not getting it right. Here's what I did:

matpwcorr gini dcps rir gdppcgr gdppc inf fsd liq
mat list corr
mat list pv
mat list pv_sidak
mat list pv_bonf

...and all the outputs came out seperately. So I used this code, since I need the pvalues alongside the correlation values:

matpwcorr gini dcps rir gdppcgr gdppc inf fsd liq, pv

...and I got this error message:

option pv not allowed
r(198);

....and then I tried this:

matpwcorr gini dcps rir gdppcgr gdppc inf fsd liq
return list
matrix list r(C)
putexcel A1=matrix(r(C), names) using C:\Users\ng\Desktop\BCII, modify

....and only the correlation values between fsd and liq are on the excel sheet.

So definitely, I am offtrack! Kindly help me out, thanks!