Transfer 'pcorr' partial correlation results into Excel

[shrister]

Hey everyone,

I am fairly new to STATA, and rate myself as a beginner at this point. I am currently running the 'pcorr' command to get age-adjusted correlations for some variables of mine. A sample command line for my work would be, 'pcorr Distal_CCA mpcinco Age'. The results table I get consists of 5 columns; the first column (partial correlation) and 5th column (significance value) are the only 2 result sections I care about. Is there a way to automatically transfer the results I care about into cells in Excel?

If my first question is possible, then I have a follow up to it. I have to run that similar command about 25 times, each time changing the location of the "mpcinco" variable with another variable. Is it possible to now run all the pcorr age-adjusted correlations at one time and transfer all the sections I care about into the Excel file? This would be much better than running the pcorr command for one variable, then the command to move it to Excel, followed by running another pcorr command and the command to move it to Excel, and again 23 more times.

[ben earnhart]

Unfortunately, even with advances in Stata output functions like outreg, estout, tabout, etc., exporting Stata output can be no fun and nobody that I know of has paid attention to correlation/covariance matrices. What I think might work best in your case is to create a log file (in the log format) then import the log into your spreadsheet software as fixed-width ASCII, which will give you a chance to ditch extraneous stuff. Does that make sense? I can give greater detail if needed.

[shrister]

Unfortunately, even with advances in Stata output functions like outreg, estout, tabout, etc., exporting Stata output can be no fun and nobody that I know of has paid attention to correlation/covariance matrices. What I think might work best in your case is to create a log file (in the log format) then import the log into your spreadsheet software as fixed-width ASCII, which will give you a chance to ditch extraneous stuff. Does that make sense? I can give greater detail if needed.

Hey thanks for the response! It still quite does not make sense to me, greater detail would be appreciated. Thanks.

[Martin Bresslein]

It would be relatively straightforward if you only cared about the first column, since it is stored as a matrix in the return list. You can easily export that matrix to an excel file using the

putexcel

command - provided you have access to Stata 13. Otherwise you could put the matrix into a dataset and use

export excel

to save it to an excel file. The problem in your case - at least as far as I see it, some of the more tenured members here might correct me - is that the "significance" column is not stored in the return list, so I am not sure how to access it to automate your export (apart from adding a few lines in the

pcorr

ado-file).
What Ben means - correct me if I misunderstood - is, you should generate a log-file for what you do in Stata using the

log

command, in this case just for your

pcorr

, e.g.:

log using "filepath/pcorr.log", text replace
pcorr var1 var2 
pcorr var2 var3
pcorr var3 var4
etc.
log close

From your spreadsheet software, you can then import this log-file (import data or something similar), using fixed-width ASCII as the data format. That way, you can actually choose what to import and what to suppress.
Hope this helps. Maybe somebody else knows how to get the significance levels, then

putexcel

would be the way to go...

[Nick Cox]

A limited exception to Ben's statement in #2 is that corrci (SJ) offers an option to save correlations to a new dataset. That doesn't help here.

[ben earnhart]

I think Martin's example about covers details on the approach I recommended. One thing to note is that the file ending is important. If the file ending for the log is ".log" you get ASCII which is ugly but easy to work with in other programs such as Excel. Any other file ending, you get .smcl, whichis pretty, but programs other than Stata don't know how to handle. Also, (at least with Excel) no choice at import-time about what columns to import. However, you specify where the columns begin and end, then just delete the portions you don't want after import.

Which gives me an idea if this were an ongoing need. I can envision a Stata .do file that uses -infile- or -infix- to read in the .log files and chop them up there, then spit out Excel. But unless you are in a production environment, running the same report weekly, not worth the trouble.

[ben earnhart]

I was on hold for a while, came up with Stata code to do what you need. Assume x20 is age. You didn't specify if the age partial correlation is of interest, but based on the -drop- command, you could nuke that, too, with adding | trim(theCorr)=="x20"

*=======running pcorrs to logfile
log using temp1.log, replace
set more off
foreach var of varlist x2-x19 {
    pcorr x1 `var' x20
}

log close

clear
*=====read in logfile
infix str11 thecorr 1-11 str6  partcorr 17-23  str6 thesig 73-78 using temp1.log
*=====drop extraneous stuff
drop if _n<14 | trim(thecorr)=="-----------" | trim(thecorr)=="Variable" | trim(thecorr)=="" ///
 | trim(thecorr)=="Partial and" | trim(partcorr)==""
 
 *====save to excel, done!
 export excel using "pcorrs", replace

[shrister]

Hey everyone, I really appreciate all your help. I am going to try out some of the codes you have written and I will update you on my progress.