BookmarkSubscribeRSS Feed
jlyb
Calcite | Level 5

Environment: SAS Studio 9.4

 

PROC CORR data=have;

    var Y logY;

    with X_001-X_100;

run;

 

In working on a dataset with hundreds of variables, is there a way to sort the Pearson correlation coefficients with the response variable, 'y', in decreasing order through PROC CORR? Instead of sorting the output as X_001, X_002, ..., X_100 and their corresponding Pearson correlation coefficients, sort by the magnitude of coefficients.

 

Several web searches have shown syntax such as 'DESCENDING' or 'RANK' but does not seem to work. 

4 REPLIES 4
rogerjdeangelis
Barite | Level 11
All pairs of variable correlations with correlation, count and p-value in descending order (spearman)

Could not run in WPS due to record limit(no limit with R).

This is code from the oto_voodoo macro(you can just extract it).

see
https://www.dropbox.com/s/po3ahepe7r7dnm7/oto_voodoo.sas?dl=0


HAVE ( sashelp.iris)
====================

p to 40 obs from sashelp.iris total obs=150

bs    SPECIES    SEPALLENGTH    SEPALWIDTH    PETALLENGTH    PETALWIDTH

 1    Setosa          50            33             14             2
 2    Setosa          46            34             14             3
 3    Setosa          46            36             10             2
 4    Setosa          51            33             17             5
 5    Setosa          55            35             13             2
...

WANT
====

Variable Correlations (Spearman)

               Correlated     Correlation    Number    Spearman
 Variable         With            Coef       of Obs       P

PETALWIDTH     PETALLENGTH      0.93767        150      <.0001
PETALLENGTH    SEPALLENGTH      0.88190        150      <.0001
PETALWIDTH     SEPALLENGTH      0.83429        150      <.0001
PETALLENGTH    SEPALWIDTH       0.30964        150      <.0001
PETALWIDTH     SEPALWIDTH       0.28903        150      <.0001
SEPALWIDTH     SEPALLENGTH      0.16678        150      <.0001


WORKING CODE
============

%_vdo_cor(
       lib=sashelp
      ,mem=iris
      );

FULL SOLUTION
=============

%macro _vdo_cor(
       lib=&libname
      ,mem=&data
      );

    data _vcor0th/view=_vcor0th;
      set %str(&lib).%str(&mem) (keep=_numeric_);
      _rec=_n_;
      if _n_=1 then _rec=.;
    run;

    ods exclude all;
    ods output spearmancorr=_vvcor1st;
    proc corr data=_vcor0th (keep=_numeric_) spearman;
       var _numeric_;
       with _numeric_;
    run;
    ods select all;

    proc sql noprint;select count(*) into :_vv_num separated by ' ' from _vvcor1st;quit;

    %put &=_vv_num;

    data _vvcor2nd;
      keep var wth n val p_rec;
      set _vvcor1st(drop=label );
      array num[*] _numeric_;
      do _i_=1 to &_vv_num;
        if num[_i_] ne . then do;
           var=variable;
           wth=vname(num[_i_]);
           n=num[_i_+ %eval(2 * &_vv_num)];
           val=abs(num[_i_]);
           if (_i_ < _n_
          and not (var='_REC' or wth = '_REC')) then output;
        end;
      end;
    run;

    proc sort data=_vvcor2nd out=vv_corsrt;
    by descending val;
    run;

    title "Variable Correlations (Spearman)";
    proc print data=vv_corsrt(obs=100) noobs width=min label split='#';
    label
        var = "Variable"
        wth = "Correlated#With"
        val = "Correlation#Coef"
        n   = "Number of Obs"
        p_rec   = "Spearman P";
    var var wth val n p_rec;
    run;
    ods select all;


%mend _vdo_cor;


%_vdo_cor(
       lib=sashelp
      ,mem=iris
      );

collinelliot
Barite | Level 11

I don't think there is any option that will generate the desired results directly from the proc. You'll have to output the correlations and then manipulate the data in some way. The code below is one example that may or may not be what you want, based on my interpretation of your question. 

 

data have;
    array x_{100} x_001 - x_100;
    do i = 1 to 50;
        y = ranuni(4) * 100;
        logy = log(y);
        do j = 1 to 100;
            x_(j) = ranuni(4) * 100;
        end;
        output;
    end;
run;

ods output PearsonCorr = corrs;

proc corr data = have nosimple;
    var y logy;
    with x_:;
   
run;

ods output close;

proc sql;
    SELECT * FROM corrs
    ORDER BY py descending;
quit;
art297
Opal | Level 21

The following requires some hard coding, but I think will provide what you want:

 

ods output PearsonCorr=need;
proc corr data=sashelp.cars nosimple noprob;
  var _numeric_;
run;

data need (drop=label nmsrp--nlength);
  set need;
run;

proc transpose data=need out=want;
  by variable notsorted;
run;

data want (drop=_:);
  length variables $67;
  set want (rename=(col1=corr));
  array vnames(2) $32. variable _name_;
  call sortc(of vnames(*));
  if vnames(1) ne vnames(2);
  variables=catx(' X ',vnames(1),vnames(2));
  abscorr=abs(corr);
run;

proc sort data=want out=want (drop=abscorr variable) nodupkey;
  by descending abscorr variables;
run;

Art, CEO, AnalystFinder.com

 

ChrisHemedinger
Community Manager

You might also be interested in how to build a correlations heat map -- should work fine in SAS Studio.  The blog post provides a macro you can use, and the data might be useful for your other purposes.

 

corr heatmap

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 2779 views
  • 6 likes
  • 5 in conversation