Adding P-value inside Pearson correlation the heat map or Stars for significance

[Huss]

Hi Community,

I am having trouble adding the P-value inside the Pearson correlation heat map. Can anyone help me with that please? Below is part of my data and the code I am using to create the heat map but I do not know how to include the P-value statement .

I really appreciate your help

Hussien

data work.Almtarfi_2;
input Cult Biomass_BRC Biomass_RB yield_BRC Yield_RB Height_BRC Heigh_RB;
CARDS;
1930 62.125 75.255 2563.16 2196.06 123.075 142.8
1933 60.62 77.91 2396.49 1965.79 106.233 124.325
1935 51.275 77.2 2317.14 2490.56 114.75 137.25
1940 63.31 73.025 2738.19 2888.38 142.85 169.5

/*Code below by Chris Hemedinger, found on the SAS blogs*/

/* Prepare the correlations coeff matrix: Pearson's r method */
%macro prepCorrData(in=,out=);
/* Run corr matrix for input data, all numeric vars */
proc corr data=&in. noprint
pearson
outp=work._tmpCorr
vardef=df
;
run;

/* prep data for heat map */
data &out.;
keep x y r;
set work._tmpCorr(where=(_TYPE_="CORR"));
array v{*} _numeric_;
x = _NAME_;
do i = dim(v) to 1 by -1;
y = vname(v(i));
r = v(i);
/* creates a lower triangular matrix */
if (i<_n_) then
r=.;
output;
end;
run;

proc datasets lib=work nolist nowarn;
delete _tmpcorr;
quit;
%mend;

/* Create a heat map implementation of a correlation matrix */
ods path work.mystore(update) sashelp.tmplmst(read);

proc template;
define statgraph corrHeatmap;
dynamic _Title;
begingraph;
entrytitle _Title;
rangeattrmap name='map';
/* select a series of colors that represent a "diverging" */
/* range of values: stronger on the ends, weaker in middle */
/* Get ideas from http://colorbrewer.org */
range -1 - 1 / rangecolormodel=(cxD8B365 cxF5F5F5 cx5AB4AC);
endrangeattrmap;
rangeattrvar var=r attrvar=r attrmap='map';
layout overlay /
xaxisopts=(display=(line ticks tickvalues))
yaxisopts=(display=(line ticks tickvalues));
heatmapparm x = x y = y colorresponse = r /
xbinaxis=false ybinaxis=false
name = "heatmap" display=all;
continuouslegend "heatmap" /
orient = vertical location = outside title="Pearson Correlation";
endlayout;
endgraph;
end;
run;

/* Build the graphs */
ods graphics /height=600 width=800 imagemap;

%prepCorrData(in=work.Almtarfi_2,out=work.Almtarfi_r);
proc sgrender data=work.Almtarfi_r template=corrHeatmap;
dynamic _title="Correlation matrix for all variables";
run;

ods graphics off;

[Rick_SAS]

Please always provide a reference if you are using code from someone else. It enables the experts to review how the code works. For this example, it appears that the code is from @ChrisHemedinger 's blog post "How to build a correlations hear map with SAS."

[Huss]

Thank you so much Rick. Really appreciate your help. Can you still help me with  including the P-value statement to the code, please?

Kind Regards

[Reeza]

Where do you want the p-values? Inside the squares? Or do you want asterisks to categorize p-values? I tried that, it was way too busy IMO.

That's actually a pretty old post (6+ years), there's easier ways in SGPLOT now, is there a specific reason you've chosen to use SGRENDER and GTL instead of SGPLOT?

[Huss]

Hi Reeza,

Thank you so much for your help. The SAS I am using is SAS 9.4 TS leveL 1M5. I want either P-value or the star (if is possible and can fit)  inside the square, please? The only reason to use this code is because I could not find another one . Therefore, if you could help me with another one that is easier to deal with , I would really really appreciate it. Again I really appreciate your time

Thanks

Hussien

[Reeza]

Make sure to search here. You can restrict your Google searches to this site by using "heatmap correlation site:communities.sas.com" as your search term.

https://communities.sas.com/t5/Graphics-Programming/Removing-one-of-the-legends-from-a-heatmap/m-p/4...

Apparently I answered this question about a year ago 😉

[JeffMeyers]

Here is my solution.  I used ODS OUTPUT to get the p-values and TEXTPLOT to graph them.

data work.Almtarfi_2;
input Cult Biomass_BRC Biomass_RB yield_BRC Yield_RB Height_BRC Heigh_RB;
CARDS;
1930 62.125 75.255 2563.16 2196.06 123.075 142.8
1933 60.62 77.91 2396.49 1965.79 106.233 124.325
1935 51.275 77.2 2317.14 2490.56 114.75 137.25
1940 63.31 73.025 2738.19 2888.38 142.85 169.5



/*Code below by Chris Hemedinger, found on the SAS blogs*/
ods trace off;
/* Prepare the correlations coeff matrix: Pearson's r method */
%macro prepCorrData(in=,out=);
/* Run corr matrix for input data, all numeric vars */
ods select none; ods noresults;
ods output PearsonCorr=blah;
proc corr data=&in. pearson 
outp=work._tmpCorr
vardef=df
;
run;
ods results;
ods select all;
proc sql noprint;
    select variable into :vlist separated by '|' from blah;
quit;
/* prep data for heat map */
data &out.;
keep x y r p;
set blah (rename=(variable=_name_));
*set work._tmpCorr(where=(_TYPE_="CORR"));
array v{*} %do i = 1 %to %sysfunc(countw(%superq(vlist),|)); %scan(%superq(vlist),&i,|) %end;;
array pval{*} %do i = 1 %to %sysfunc(countw(%superq(vlist),|)); p%scan(%superq(vlist),&i,|) %end;;
x = _NAME_;
do i = dim(v) to 1 by -1;
y = vname(v(i));
r = v(i);
p = pval(i);

/* creates a lower triangular matrix */
if (i<_n_) then do;
r=.;p=.;
end;
output;
end;
run;

proc datasets lib=work nolist nowarn;
delete _tmpcorr;
quit;
%mend;
options mprint;
/* Create a heat map implementation of a correlation matrix */
ods path work.mystore(update) sashelp.tmplmst(read);

proc template;
define statgraph corrHeatmap;
dynamic _Title;
begingraph;
entrytitle _Title;
rangeattrmap name='map';
/* select a series of colors that represent a "diverging" */
/* range of values: stronger on the ends, weaker in middle */
/* Get ideas from http://colorbrewer.org */
range -1 - 1 / rangecolormodel=(cxD8B365 cxF5F5F5 cx5AB4AC);
endrangeattrmap;
rangeattrvar var=r attrvar=r attrmap='map';
layout overlay /
xaxisopts=(display=(line ticks tickvalues))
yaxisopts=(display=(line ticks tickvalues));
heatmapparm x = x y = y colorresponse = r /
xbinaxis=false ybinaxis=false
name = "heatmap" display=all;
textplot x=x y=y text=p;
continuouslegend "heatmap" /
orient = vertical location = outside title="Pearson Correlation";
endlayout;
endgraph;
end;
run;

/* Build the graphs */
ods graphics /height=600 width=800 imagemap;

%prepCorrData(in=work.Almtarfi_2,out=work.Almtarfi_r);
proc sgrender data=work.Almtarfi_r template=corrHeatmap;
dynamic _title="Correlation matrix for all variables";
run;

[Huss]

Hi JeffMeyers,

Thank you so much for your help. When running the code , I am getting errors as below:

MPRINT(PREPCORRDATA): data work.Almtarfi_r;
MPRINT(PREPCORRDATA): keep x y r p;
MPRINT(PREPCORRDATA): set blah (rename=(variable=_name_));
ERROR: File WORK.BLAH.DATA does not exist.
MPRINT(PREPCORRDATA): *set work._tmpCorr(where=(_TYPE_="CORR"));
WARNING: Apparent symbolic reference VLIST not resolved.
MPRINT(PREPCORRDATA): array v{*};
ERROR: The array v has been defined with zero elements.
WARNING: Apparent symbolic reference VLIST not resolved.
MPRINT(PREPCORRDATA): array pval{*};
ERROR: The array pval has been defined with zero elements.
MPRINT(PREPCORRDATA): x = _NAME_;
MPRINT(PREPCORRDATA): do i = dim(v) to 1 by -1;
ERROR: Too many array subscripts specified for array v.
MPRINT(PREPCORRDATA): y = vname(v(i));
MPRINT(PREPCORRDATA): r = v(i);
ERROR: Too many array subscripts specified for array v.
MPRINT(PREPCORRDATA): p = pval(i);
ERROR: Too many array subscripts specified for array pval.
MPRINT(PREPCORRDATA): if (i<_n_) then do;
MPRINT(PREPCORRDATA): r=.;
MPRINT(PREPCORRDATA): p=.;
MPRINT(PREPCORRDATA): end;
MPRINT(PREPCORRDATA): output;
MPRINT(PREPCORRDATA): end;
MPRINT(PREPCORRDATA): run;

Can you please, help to see whey I have all these errors, please?

Really appreciate your help

[JeffMeyers]

What happens when you run the PROC CORR with the ODS OUTPUT statement that I put before it? And then the PROC SQL code that I had listed?

[Huss]

When running the code below: 

ods output PearsonCorr=blah;
ods select none; ods noresults;
proc corr data=&in. pearson
outp=work._tmpCorr
vardef=df
;
run;
ods results;
ods select all;
proc sql noprint;
select variable into :vlist separated by '|' from blah;
quit;

I got this :

2476 ods results;
2477 ods select all;

2478 proc sql noprint;
2479 select variable into :vlist separated by '|' from blah;
ERROR: File WORK.BLAH.DATA does not exist.
2480 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Is there any hunt, please?

Thanks

[JeffMeyers]

It looks like the actual PROC CORR isn't running. Try deleting the comments around the procedure to make sure the procedure isn't accidentally being commented out.

[Huss]

Hi Jeff,

I only got the heat map matrix as below,

[JeffMeyers]

Can you copy and paste the exact code you're running now, just so I can check if you're running the same thing that I posted?

[Huss]

Hi Jeff,

Below is the code I copyied and used after deleting comments:

data work.Almtarfi_2;
input Cult Biomass_BRC Biomass_RB yield_BRC Yield_RB Height_BRC Heigh_RB;
CARDS;
1930 62.125 75.255 2563.16 2196.06 123.075 142.8
1933 60.62 77.91 2396.49 1965.79 106.233 124.325
1935 51.275 77.2 2317.14 2490.56 114.75 137.25
1940 63.31 73.025 2738.19 2888.38 142.85 169.5

ods trace off;

%macro prepCorrData(in=,out=);

ods output PearsonCorr=blah;
ods select none; ods noresults;
proc corr data=&in. pearson
outp=work._tmpCorr
vardef=df;
run;
ods results;
ods select all;
proc sql noprint;
select variable into :vlist separated by '|' from blah;
quit;

data &out.;
keep x y r p;
set blah (rename=(variable=_name_));

array v{*} %do i = 1 %to %sysfunc(countw(%superq(vlist),|)); %scan(%superq(vlist),&i,|) %end;;
array pval{*} %do i = 1 %to %sysfunc(countw(%superq(vlist),|)); p%scan(%superq(vlist),&i,|) %end;;
x = _NAME_;
do i = dim(v) to 1 by -1;
y = vname(v(i));
r = v(i);
p = pval(i);

if (i<_n_) then do;
r=.;p=.;
end;
output;
end;
run;

proc datasets lib=work nolist nowarn;
delete _tmpcorr;
quit;
%mend;
options mprint;

ods path work.mystore(update) sashelp.tmplmst(read);

proc template;
define statgraph corrHeatmap;
dynamic _Title;
begingraph;
entrytitle _Title;
rangeattrmap name='map';

range -1 - 1 / rangecolormodel=(cxD8B365 cxF5F5F5 cx5AB4AC);
endrangeattrmap;
rangeattrvar var=r attrvar=r attrmap='map';
layout overlay /
xaxisopts=(display=(line ticks tickvalues))
yaxisopts=(display=(line ticks tickvalues));
heatmapparm x = x y = y colorresponse = r /
xbinaxis=false ybinaxis=false
name = "heatmap" display=all;
textplot x=x y=y text=p;
continuouslegend "heatmap" /
orient = vertical location = outside title="Pearson Correlation";
endlayout;
endgraph;
end;
run;

ods graphics /height=600 width=800 imagemap;

%prepCorrData(in=work.Almtarfi_2,out=work.Almtarfi_r);
proc sgrender data=work.Almtarfi_r template=corrHeatmap;
dynamic _title="Correlation matrix for all variables";
run;

[JeffMeyers]

OK I ran what you had and got the same errors.  I fixed it by changing the order before the proc corr.  Put the ODS OUTPUT after the ODS NORESULTS statement.

ods select none; ods noresults;
ods output PearsonCorr=blah;