BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lizzy28
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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


View solution in original post

2 REPLIES 2
Astounding
PROC Star

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
Barite | Level 11
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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1481 views
  • 3 likes
  • 3 in conversation