topic How to create a table of correlations for many subgroups? in SAS Procedures

How to create a table of correlations for many subgroups?

huhuhu — Mon, 29 Aug 2016 15:37:14 GMT

Dear all SAS experts,

I'd like to have a table of correlation between two variables (for example, x and y) by many groups. My dream matrix would like this:

GroupNumber Correlation between x and y

1 0.5153

2 0.6

3 0.3

4 0.6

5 0.2

I can use BY statement in PROC CORR procedures, but I would get many seperate tables for each subgroup in the BY variable like below (an example result for groupNumber 1), and it's time consuming to build my dream table by hand since it comprosed of more than 100 sugroups each year.

Pearson Correlation Coefficients, N = 8996452
Prob > \|r\| under H0: Rho=0
	x	y
x	1	0.5153

y	0.5153	1

Any suggestions would be appreciated!

Re: How to create a table of correlations for many subgroups?

rayIII — Mon, 29 Aug 2016 16:02:22 GMT

This will give you a table (corr) of all of the coefficients. You'll just need to eliminate the redundant ones.

proc corr data = sashelp.iris;
var petallength sepallength;
by species;
ods output pearsonCorr = Corr; 
run;

hope this helps.

Ray

Re: How to create a table of correlations for many subgroups?

Ksharp — Tue, 30 Aug 2016 04:45:26 GMT

Want IML code ?



proc iml;
use sashelp.iris nobs nobs;
read all var{species petallength sepallength};
close;

start_end=t( loc(t(species) ^= ' '||remove(species,nobs)) )||
          t( loc(t(species) ^= remove(species,1)||' ') );

group=species[start_end[,1]];
corr=j(nrow(group),1,.);
do i=1 to nrow(group);
 idx=start_end[i,1]:start_end[i,2]; 
 temp=petallength[idx]|| sepallength[idx];
 corr[i]=corr(temp)[2];
end;
create want from corr[r=group c={corr}];
append from corr[r=group];
close;
quit;

proc print noobs;run;



OUTPUT:

group	CORR
Setosa	0.26718
Versicolor	0.75405
Virginica	0.86422

Re: How to create a table of correlations for many subgroups?

Rick_SAS — Tue, 30 Aug 2016 12:25:31 GMT

You can use the NOPRINT and OUT= options to suppress the tables and create a SAS data set that contains the correlations. Then use a DATA step or a WHERE clause to subset the output statistics:

proc corr data = sashelp.iris noprint 
          out=outCorr(where=(_TYPE_="CORR"));
by species;
var petallength sepallength;
run; 

proc print data=outCorr label;
where _NAME_="PetalLength";  /* var1 */
var Species                  /* BY var */
    SepalLength;             /* var2 */
label SepalLength="Corr(PetalLength, SepalLength)";
run;

Re: How to create a table of correlations for many subgroups?

Ksharp — Tue, 30 Aug 2016 13:20:28 GMT

Rick,
It could be simple as :


proc corr data = sashelp.iris noprint 
          out=outCorr(where=(_TYPE_="CORR"));
by species;
var petallength ;
with sepallength;
run; 
proc print noobs;run;

Re: How to create a table of correlations for many subgroups?

huhuhu — Thu, 01 Sep 2016 18:46:15 GMT

Thank you, that's the dream output I like to have!

Re: How to create a table of correlations for many subgroups?

huhuhu — Thu, 01 Sep 2016 18:46:59 GMT

Thank you very much! That's even simpler!

Re: How to create a table of correlations for many subgroups?

huhuhu — Thu, 01 Sep 2016 18:54:51 GMT

Thank you for your help