topic Re: Combining 2 correlation tables into 1. (1 above, 1 below the diagonal) in SAS Programming

Combining 2 correlation tables into 1. (1 above, 1 below the diagonal)

mcook — Tue, 27 Sep 2022 15:23:14 GMT

I am using SAS EG.

Given 2 tables similar to the following Temp1 and Temp2.

data Temp1;
input Col1 $4. Var1 Var2 Var3 Var4 5.2;
datalines;
Var1  1.0 0.45 0.22 0.89
Var2 0.45 1.0 0.54 0.94
Var3 0.22 0.54 1.0 0.33
Var4 0.89 0.94 0.33 1.0 
;
run;

data Temp2;
input Col1 $4. Var1 Var2 Var3 Var4 5.;
datalines;
Var1 0 213 17 409
Var2 213 0 78 55
Var3 17 78 0 300
Var4 409 55 300 0 
;
run;

is there a way to combine the 2 tables with one set of data being above the diagonal, and the other below? as in the above table "Combined".

I've read a few posts along these lines that require SAS/IML. But i do not have access to IML. Is it possible without IML?

Re: Combining 2 correlation tables into 1. (1 above, 1 below the diagonal)

mcook — Tue, 27 Sep 2022 15:24:06 GMT

I forgot to add the "Combined" Table example above.. Apologies

Data Combined;
input Col1 $4. Var1 Var2 Var3 Var4 5.2;
datalines;
Var1 . 0.45 0.22 0.89
Var2 213 . 0.54 0.94
Var3 17 78 . 0.33
Var4 409 55 300 . 
;
run;

Re: Combining 2 correlation tables into 1. (1 above, 1 below the diagonal)

Tom — Tue, 27 Sep 2022 16:27:49 GMT

Just use arrays.

data above;
  input Col1 $ Var1-Var4 ;
datalines;
Var1  1.0 0.45 0.22 0.89
Var2 0.45 1.0 0.54 0.94
Var3 0.22 0.54 1.0 0.33
Var4 0.89 0.94 0.33 1.0 
;

data below ;
  input Col1 $ Var1-Var4 ;
datalines;
Var1 0 213 17 409
Var2 213 0 78 55
Var3 17 78 0 300
Var4 409 55 300 0 
;


data want ;
  merge below above(rename=(var1-var4=above1-above4));
  by col1;
  array var [4];
  array above [4];
  var[_n_] = .;
  do index=_n_+1 to dim(var);
    var[index]=above[index];
  end;
  drop index above1-above4 ;
run;

proc print;
run;

Result

Obs    Col1    var1     var2      var3    var4

 1     Var1       .     0.45      0.22    0.89
 2     Var2     213      .        0.54    0.94
 3     Var3      17    78.00       .      0.33
 4     Var4     409    55.00    300.00     .

PS Do not use a decimal width on an informat unless you know that the strings you are reading from were purposely built without an explicit decimal to save one byte. Otherwise strings without periods will be divided by that number of powers of 10 to move the decimal place to the implied position.

Re: Combining 2 correlation tables into 1. (1 above, 1 below the diagonal)

ballardw — Tue, 27 Sep 2022 16:18:02 GMT

This works with the example data:

data want;
   merge temp1
         temp2 (rename=(var1=v1 var2=v2 var3=v3 var4=v4))
   ; 
   by col1;
   select (upcase(col1));
      when('VAR1') do;  var1=.; 
                   end;
      when('VAR2') do;  var2=.;
                        var1=v1;
                   end;
      when('VAR3') do;  var3=.;
                        var1=v1;
                        var2=v2;
                   end;
      when('VAR4') do;  var4=.;
                        var1=v1;
                        var2=v2;
                        var3=v3;
                   end;
      otherwise;
   end;
   keep col1 var: ;
run;

Depending on your actual variable names you may have to use the NOTSORTED option on the BY Col1 ; statement.

SELECT, if you have not seen that before is a way to do a bunch of If value= do this. In this form the WHEN is a single value for the row indicator. I use an UPCASE function on the COL1 variable just in case the case of the variable is inconsistent.

This is pretty much a code equivalent of look at row1 and select the values to put in the columns, then row2 etc. This can get pretty cumbersome if you have 50 variables involved though.

Re: Combining 2 correlation tables into 1. (1 above, 1 below the diagonal)

Rick_SAS — Thu, 29 Sep 2022 09:47:23 GMT

Speaking of looping over elements in a correlation matrix, I recently wrote about how to convert the correlation matrix to a "long format" that shows the pairwise correlations in a list format:

"Display correlations in a list format"

The DATA step loop for that purpose is similar to @Tom's loop here since it processes the same configuration of data.