Proc corr / output dataset with CORR and p-value in separate columns

[Cruise]

Hi Folks:

I'm trying to create a data of CORR and it's corresponding p-values from proc corr. Outp option outputs only CORR that I need but not p-value. 

Any suggestion as to how I could achieve the DESIRED OUTPUT below? Mock dataset attached to this post with two example variables and an outcome variable 'log_outcome'. I have many variables in actuality so that re-organizing in Excel sheet manually is not ideal.  

proc corr data=have outp=outp;
var log_outcome houses rentals;
run;  

[WarrenKuhfeld]

Create an ODS output data set then use a DATA step to reformat the table to your liking.

[ed_sas_member · Accepted solution]

Hi @Cruise 

You can retrieve correlation coefficients as well as values by using 

ods output PearsonCorr=_PearsonCorr;

Here is the list of tables that you can output from PROC CORR (https://documentation.sas.com/?docsetId=procstat&docsetTarget=procstat_corr_details24.htm&docsetVers...)

 

In your case, I would suggest something like this:

ods output PearsonCorr=_PearsonCorr;

proc corr data=have outp=outp pearson ;
	var houses rentals;
	with log_outcome;
run;  

/* Retrieve p-values */
proc transpose data=_PearsonCorr out=_pvalue(rename=(col1=pvalue)) name=_variables;
	var Phouses Prentals;
run;

data _pvalue2;
	set _pvalue;
	variables = substr(_variables,2);
	drop _:;
run;

/* Retrieve correlation coefficients */
proc transpose data=_PearsonCorr out=_corr(rename=(col1=corr)) name=variables;
	var houses rentals;
run;

/* Merge */
data want;
	length variables $ 20;
	merge _corr _pvalue2;
	by variables;
	label variables = "Variables"
	      corr = "Corr"
	      pvalue = "p-value";
run;

/* Report */
proc report data=want nowd;
	columns variables ("LOG_OUTCOME" corr pvalue);
run;

Best,