Get lower triangle of correlation matrix

[lizzy28]

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

[Astounding]

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.

[rogerjdeangelis · Accepted solution]

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