Hi All,
I tried to get the lower triangle of a correlation matrix with the code below. When I used the variables and specific number of variables (do i=1 to 3 instead of do i=1 to dim(pval) ), the coding worked fine. When I tried to use macro variable and dim() function as below, the output was misreplaced (having wrong correlation values except the first two columns). Could anyone help me correct my code?
%let XVARS=
var1 var2 var3;
**Get full correlation matrix;
ods output PearsonCorr=fullcorr;
proc corr data=mdl;
var &XVARS.;
run;
**Keep statistically significant coefficients;
data corr;
set fullcorr;
array pval{*} p&XVARS.;
array vrbl{*} &XVARS.;
do i=1 to dim(pval);
if pval[i]>=0.05 then vrbl[i]=.;
end;
run;
data corr;
set corr
(keep=variable label &XVARS.);
run;
**Get lower triangle matrix;
data lower_tri;
set corr;
array vrbl{*} &XVARS.;
do _i=1 to dim(vrbl);
if (_i>_n_) then vrbl[_i]=.;
end;
drop _i;
run;
Thanks as always!
Lizi
Selecting only lower tiangular correlations with p values <= .05
You can cut and paste the R code into IML/R
or just use IML
1. SAS Using proc corr (too many observartions for WPS express - unable to test)
2. SAS/WPS/R
One of the problems is
%let XVARS=SEPALLENGTH SEPALWIDTH PETALLENGTH;
P&xvars is not what you need
%put P&xvars;
PSEPALLENGTH SEPALWIDTH PETALLENGTH
You need
%let PXVARS=PSEPALLENGTH PSEPALWIDTH PPETALLENGTH;
HAVE This correlation matrix
=============================
%let XVARS=SEPALLENGTH SEPALWIDTH PETALLENGTH;
%let PXVARS=PSEPALLENGTH PSEPALWIDTH PPETALLENGTH;
%let dimsqr=%sysfunc(countw(&xvars));
Up to 40 obs from fullcorr total obs=3
CORRELATIONS PVALUE
======================================= ==========================================
VARIABLE SEPALLENGTH SEPALWIDTH PETALLENGTH PSEPALLENGTH PSEPALWIDTH PPETALLENGTH
SEPALLENGTH 1.00000 -0.11757 0.87175 _ 0.15190 0
SEPALWIDTH -0.11757 1.00000 -0.42844 0.15190 _ 0
PETALLENGTH 0.87175 -0.42844 1.00000 0.00000 0.00000 _
WANT
====
SAS/WPS proc corr
=================
SEPALLENGTH SEPALWIDTH PETALLENGTH
row=1 1.00000 . .
row=2 . 1.00000 .
row=3 0.87175 -0.42844 1.00000
DETAILS (SAS/WPS operations on correlation matrix)
1. Set upper triangular to missing
2. keep 1s on diagonal
3. set correlations to missing for 0.15190 because 0.15190 ge to 0.05.
see below
PVALUE
==========================================
PSEPALLENGTH PSEPALWIDTH PPETALLENGTH
_ 0.15190 0
0.15190 _ 0
0.00000 0.00000 _
R
==
SEPALLENGTH SEPALWIDTH PETALLENGTH
SEPALLENGTH 1.0000000 NA NA
SEPALWIDTH 0.0000000 1.0000000 NA
PETALLENGTH 0.8717538 -0.4284401 1
SOLUTION
========
*____ _ ____
/ ___| / \ / ___|
\___ \ / _ \ \___ \
___) / ___ \ ___) |
|____/_/ \_\____/
;
* get the correlation matrix with pvalues;
%let XVARS=SEPALLENGTH SEPALWIDTH PETALLENGTH;
%let PXVARS=PSEPALLENGTH PSEPALWIDTH PPETALLENGTH;
%let dimsqr=%sysfunc(countw(&xvars));
/* dimsqr = 3 */
**Get full correlation matrix;
ods output PearsonCorr=fullcorr;
proc corr data=sashlp.iris(keep=sepallength sepalwidth petallength);
var &XVARS.;
run;
* load into arrays;
data want(keep=&xvars);
retain n 0;
array pval{*} &pXVARS.;
array vrbl{*} &XVARS.;
array cor(&dimsqr,&dimsqr) _temporary_;
array pvl(&dimsqr,&dimsqr) _temporary_;
do until(dne);
set fullcorr end=dne;
n=n+1;
do j=1 to &dimsqr.;
cor[n,j]=vrbl[j];
pvl[n,j]=pval[j];
* put
cor[n,j]=
pvl[n,j]=
;
end;
end;
do i=1 to &dimsqr.;
do j=1 to &dimsqr.;
if pvl[i,j] ge .05 then cor[i,j]=.;
if i<j then cor[i,j]=.;
end;
put "row=" i @@;
do j=1 to &dimsqr.;
put cor[i,j] @@;
vrbl[j]=cor[i,j];
end;
put;
output;
end;
run;quit;
*____
| _ \
| |_) |
| _ <
|_| \_\
;
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.iris(keep=sepallength sepalwidth petallength);
set sashelp.iris;
run;quit;
%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.3.1";
proc r;
submit;
library(haven);
library(Hmisc);
eyerus<-as.matrix(read_sas("d:/sd1/iris.sas7bdat"));
cor<-rcorr(eyerus,type=c("pearson"));
pval<- cor[[3]] < 0.05;
pval[is.na(pval)]<-FALSE;
fin<-cor[[1]]*pval;
fin[upper.tri(fin)] <- NA;
diag(fin) <- 1;
fin;
endsubmit;
run;quit;
');
The WPS System
SEPALLENGTH SEPALWIDTH PETALLENGTH
SEPALLENGTH 1.0000000 NA NA
SEPALWIDTH 0.0000000 1.0000000 NA
PETALLENGTH 0.8717538 -0.4284401 1
I suspect the problem lies with the arrays, rather than the DO loops. To illustrate, let's suppose that &XVARS contains: a b c
Then the PVAL array does not contain: pa pb pc
Rather, it contains: pa b c
So the construction of the PVAL array has to change to insert multiple instances of "p" instead of just one at the beginning.
Selecting only lower tiangular correlations with p values <= .05
You can cut and paste the R code into IML/R
or just use IML
1. SAS Using proc corr (too many observartions for WPS express - unable to test)
2. SAS/WPS/R
One of the problems is
%let XVARS=SEPALLENGTH SEPALWIDTH PETALLENGTH;
P&xvars is not what you need
%put P&xvars;
PSEPALLENGTH SEPALWIDTH PETALLENGTH
You need
%let PXVARS=PSEPALLENGTH PSEPALWIDTH PPETALLENGTH;
HAVE This correlation matrix
=============================
%let XVARS=SEPALLENGTH SEPALWIDTH PETALLENGTH;
%let PXVARS=PSEPALLENGTH PSEPALWIDTH PPETALLENGTH;
%let dimsqr=%sysfunc(countw(&xvars));
Up to 40 obs from fullcorr total obs=3
CORRELATIONS PVALUE
======================================= ==========================================
VARIABLE SEPALLENGTH SEPALWIDTH PETALLENGTH PSEPALLENGTH PSEPALWIDTH PPETALLENGTH
SEPALLENGTH 1.00000 -0.11757 0.87175 _ 0.15190 0
SEPALWIDTH -0.11757 1.00000 -0.42844 0.15190 _ 0
PETALLENGTH 0.87175 -0.42844 1.00000 0.00000 0.00000 _
WANT
====
SAS/WPS proc corr
=================
SEPALLENGTH SEPALWIDTH PETALLENGTH
row=1 1.00000 . .
row=2 . 1.00000 .
row=3 0.87175 -0.42844 1.00000
DETAILS (SAS/WPS operations on correlation matrix)
1. Set upper triangular to missing
2. keep 1s on diagonal
3. set correlations to missing for 0.15190 because 0.15190 ge to 0.05.
see below
PVALUE
==========================================
PSEPALLENGTH PSEPALWIDTH PPETALLENGTH
_ 0.15190 0
0.15190 _ 0
0.00000 0.00000 _
R
==
SEPALLENGTH SEPALWIDTH PETALLENGTH
SEPALLENGTH 1.0000000 NA NA
SEPALWIDTH 0.0000000 1.0000000 NA
PETALLENGTH 0.8717538 -0.4284401 1
SOLUTION
========
*____ _ ____
/ ___| / \ / ___|
\___ \ / _ \ \___ \
___) / ___ \ ___) |
|____/_/ \_\____/
;
* get the correlation matrix with pvalues;
%let XVARS=SEPALLENGTH SEPALWIDTH PETALLENGTH;
%let PXVARS=PSEPALLENGTH PSEPALWIDTH PPETALLENGTH;
%let dimsqr=%sysfunc(countw(&xvars));
/* dimsqr = 3 */
**Get full correlation matrix;
ods output PearsonCorr=fullcorr;
proc corr data=sashlp.iris(keep=sepallength sepalwidth petallength);
var &XVARS.;
run;
* load into arrays;
data want(keep=&xvars);
retain n 0;
array pval{*} &pXVARS.;
array vrbl{*} &XVARS.;
array cor(&dimsqr,&dimsqr) _temporary_;
array pvl(&dimsqr,&dimsqr) _temporary_;
do until(dne);
set fullcorr end=dne;
n=n+1;
do j=1 to &dimsqr.;
cor[n,j]=vrbl[j];
pvl[n,j]=pval[j];
* put
cor[n,j]=
pvl[n,j]=
;
end;
end;
do i=1 to &dimsqr.;
do j=1 to &dimsqr.;
if pvl[i,j] ge .05 then cor[i,j]=.;
if i<j then cor[i,j]=.;
end;
put "row=" i @@;
do j=1 to &dimsqr.;
put cor[i,j] @@;
vrbl[j]=cor[i,j];
end;
put;
output;
end;
run;quit;
*____
| _ \
| |_) |
| _ <
|_| \_\
;
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.iris(keep=sepallength sepalwidth petallength);
set sashelp.iris;
run;quit;
%utl_submit_wps64('
options set=R_HOME "C:/Program Files/R/R-3.3.1";
proc r;
submit;
library(haven);
library(Hmisc);
eyerus<-as.matrix(read_sas("d:/sd1/iris.sas7bdat"));
cor<-rcorr(eyerus,type=c("pearson"));
pval<- cor[[3]] < 0.05;
pval[is.na(pval)]<-FALSE;
fin<-cor[[1]]*pval;
fin[upper.tri(fin)] <- NA;
diag(fin) <- 1;
fin;
endsubmit;
run;quit;
');
The WPS System
SEPALLENGTH SEPALWIDTH PETALLENGTH
SEPALLENGTH 1.0000000 NA NA
SEPALWIDTH 0.0000000 1.0000000 NA
PETALLENGTH 0.8717538 -0.4284401 1
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.