topic Re: Create Triangular Symmetric Matrix in SAS Programming

Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 12:26:44 GMT

I have a Matrix with bottom left triangular values. The top right triangular values are empty.

How can I make this a symmetrical Matrix and add a Diagonal with Zeros?

I used this code to create the bottom left triangular values:

proc transpose data=city_distance out=work.dchart (drop=_NAME_);
var distance;
by startID;
id endID;
run;

Thanks

Re: Create Triangular Symmetric Matrix

PeterClemmensen — Tue, 26 Nov 2019 12:33:22 GMT

@hedgddfgd Hi and welcome to the SAS Community 🙂

Are you able to show us your data?

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 12:57:17 GMT

Thank you for your answer.

I attached a Picture of the data. I want a Diagonal above and the empty cells filled with the symmetrical data.

Re: Create Triangular Symmetric Matrix

Ksharp — Tue, 26 Nov 2019 13:11:56 GMT

You need post data and output .

And better post it at IML forum if it is about Matrix problem due to IML refer to Interactive MATRIX Language .

data have;
input a b c ;
cards;
1 . .
2 1 .
3 2 1 
;

proc iml;
use have ;
read all var _num_ into x;
close;
x=choose(x=.,0,x);
r=j(1,ncol(x)+1,0);
c=j(nrow(x),1,0);
temp=r//(x||c);
want=temp+t(temp);
create want from want;
append from want;
close;
quit;

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 13:15:14 GMT

Thanks a lot! But how can I Keep the names of the cols?

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 13:17:09 GMT

And also the startID in col 1

Re: Create Triangular Symmetric Matrix

Ksharp — Tue, 26 Nov 2019 13:17:49 GMT

If you add a diagonal of 0 , you would get one more variable ,how do you rename it ?

Re: Create Triangular Symmetric Matrix

Ksharp — Tue, 26 Nov 2019 13:23:44 GMT

OK. I have to leave now. Try this one.

data have;
input id $ a b c ;
cards;
a1 1 . .
b2 2 1 .
c3 3 2 1 
;

proc iml;
use have ;
read all var _num_ into x[c=vname r=id];
close; 
x=choose(x=.,0,x);
r=j(1,ncol(x)+1,0);
c=j(nrow(x),1,0);
temp=r//(x||c);
want=temp+t(temp);
id='zero'//id;
vname='zero'||vname	;
create want from want[c=vname r=id];
append from want[r=id];
close;
quit;

Re: Create Triangular Symmetric Matrix

Ksharp — Tue, 26 Nov 2019 13:29:51 GMT

If you don't want add a Diagonal with Zeros. Try this one.

I have to leave. Maybe @Rick_SAS could give you more accurate code .

data have;
input id $ a b c ;
cards;
a1 1 . .
b2 2 1 .
c3 3 2 1 
;

proc iml;
use have ;
read all var _num_ into x[c=vname r=id];
close; 
x=choose(x=.,0,x);
diag=diag(x);
want=x+t(x)-diag;

create want from want[c=vname r=id];
append from want[r=id];
close;
quit;

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 13:47:10 GMT

Thank you so much, that worked!

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 13:47:38 GMT

I'll try to learn more about IML.

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 14:44:22 GMT

Now I see when I use that the values are shifted and they are not right anymore. How can I fix that? I would need an additional row on top with startID=DE-01001

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 14:49:46 GMT

My original data now Looks like this:

Re: Create Triangular Symmetric Matrix

KachiM — Tue, 26 Nov 2019 15:26:49 GMT

@hedgddfgd

Just for fun. You can as well do this using only one Data Step. A two dimensional array is used to store the values in row-col order first. Once the Data Set is read and reached the end of file, we simply switch col-row values into row-col values. It is that simple.

data have;
   input id a b c;
datalines;
10 5  .  .
20 7  3  .
30 9  2  5
;
run;

%let rows = 3;

data want;
   array k[&rows,&rows] _temporary_;
   array m[&rows] _temporary_;
   /** Save the cells into a 2-dimensional array **/
   do i = 1 by 1 until(eof);
      set have end = eof;
      m[i] = id;
      array v a -- c;
      do j = 1 to dim(v);
         k[i, j] = v[j];
      end;
   end;
   /** Switch col-row cells into row-col cells **/
   if eof;
   do i = 1 to dim1(k);
      id = m[i];
      do j = 1 to dim2(k);
         if i = j then k[i,j] = 0;
         else k[i,j] = k[j, i];
         v[j] = k[i,j];
      end;
      output;
   end;
drop i j;
run;

The output is:

Re: Create Triangular Symmetric Matrix

Rick_SAS — Tue, 26 Nov 2019 17:30:34 GMT

In SAS/IML, you can use the VECH function to extract the lower triangular portion of the matrix and use the SQRVECH function to create the full matrix. For an example, see "Creating symmetric matrices: Two useful functions with strange names."

If necessary, you can also assign the diagonal elements of the matrix.

Re: Create Triangular Symmetric Matrix

hedgddfgd — Tue, 26 Nov 2019 16:51:36 GMT

Wher is the second link supposed to go to?

Re: Create Triangular Symmetric Matrix

Rick_SAS — Tue, 26 Nov 2019 17:31:01 GMT

Thanks. Fixed link

Re: Create Triangular Symmetric Matrix

Ksharp — Wed, 27 Nov 2019 11:24:27 GMT

OK. That would be more simple. Check if data is similar with yours.

data have;
input id $ a b c d;
cards;
aa . . . .
a1 1 . . .
b2 2 1 . .
c3 3 2 1 .
;

proc iml;
use have ;
read all var _num_ into x[c=vname r=id];
close; 
x=choose(x=.,0,x);
want=x+t(x);

create want from want[c=vname r=id];
append from want[r=id];
close;
quit;