topic How to extract some data from correlation matrix in SAS Programming

How to extract some data from correlation matrix

superbug — Thu, 07 Jul 2022 21:10:41 GMT

Below is the lower triangle of my correlation matrix.

I want to extract the correlations that >0.2 but not equal 1 (I am not interested in the diagonal 1s). My matrix is 180 x 180. What's the SAS code/function that can let me quickly get what I need?

Thanks much in advance!

Re: How to extract some data from correlation matrix

PaigeMiller — Thu, 07 Jul 2022 21:46:51 GMT

Assuming the correlation matrix is created by SAS (and your matrix looks like it is in Excel), this works fine.

https://communities.sas.com/t5/SAS-Programming/creating-a-table-of-highly-correlated-values/m-p/802045#M315701

If you just import the matrix from Excel, that code won't work without some changes, which I haven't bothered to figure out at this time.

Also I think you need to re-think this part

I want to extract the correlations that >0.2 but not equal 1

I think you do want off-diagonal terms that are equal to 1. Nevertheless, the method in that link works only on off-diagonal terms. And maybe (its up to you) you also want terms where the correlation is < –0.2.

Re: How to extract some data from correlation matrix

Ksharp — Fri, 08 Jul 2022 13:16:14 GMT

options validvarname=v7;
proc import datafile='c:\temp\have.xlsx' out=have dbms=xlsx replace;run;

data temp;
 set have;
 n+1;
run;
proc transpose data=temp out=temp2;
by n A;
run;
data want;
 set temp2;
 if 0.2<col1<1;
 keep A _name_ col1;
run;

Re: How to extract some data from correlation matrix

superbug — Fri, 08 Jul 2022 13:52:45 GMT

@Ksharp

As always, thanks a bunch!

a big thumb up to your expertise!

Re: How to extract some data from correlation matrix

superbug — Fri, 08 Jul 2022 14:34:29 GMT

@PaigeMiller

Thanks so much for your code!

Since I got my correlation matrix from another source, I need to read it into SAS.

I applied your code, but the result I got only contains the correlation between the first and the last variable.

So I run your example code step by step to check what appended in each step. My trial is as following


proc corr data=sashelp.class noprob nosimple outp=try;
    var age height weight;
/*    ods output pearsoncorr=corrs;*/
run;

data try1;
set try;
if _TYPE_="CORR";
run;

data try1;
set try1;
drop _TYPE_;
run;

data try2;
    set try1;
    length variable2 $ 32;
    array r _numeric_;
    do j=_n_+1 to dim(r);
        corr=r(j);
        variable2=vname(r(j));
end;
run;

But the result table I got is like the following. The corr column only contains the correlations from the last row. I can't figure out how to fix that. Could you please give me direction? Thank you!

Re: How to extract some data from correlation matrix

PaigeMiller — Fri, 08 Jul 2022 15:16:01 GMT

The code I gave works when you use

ods output pearsoncorr=corrs;

Instead, you comment this out and add in outp=try

and now it doesn't work.

So, the simple fix is to stop using outp=try and use

ods output pearsoncorr=corrs;

Unless you have a VERY good reason for not wanting to use the code I gave , I'm not going to further try to debug your code.

Re: How to extract some data from correlation matrix

superbug — Fri, 08 Jul 2022 15:27:56 GMT

@PaigeMiller

Thanks again for your time. I learned a lot from your code.

As I mentioned in my previous message. My correlation matrix was from another source, which saved as .csv. I used SAS to read in. If I directly apply your code, I can only get the correlations of the last row. That's why I need to figure out each step of your code, so that I can apply it or modify it as needed.

I very much appreciate your help and expertise! Thanks!

Re: How to extract some data from correlation matrix

PaigeMiller — Fri, 08 Jul 2022 17:38:40 GMT

You need to provide a portion of this CSV file, perhaps a 4x4 matrix. You need to follow these instructions: import the CSV file into SAS, and then turn it into SAS data step code via this macro and then post the code. Or you can create as working SAS data step code by typing it in yourself (please test the code before you post it so that you know it works). Do not attach the .csv file because I never download attachments.