Create Triangular Symmetric Matrix

[hedgddfgd]

Hi

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

[PeterClemmensen]

@hedgddfgd Hi and welcome to the SAS Community 🙂

Are you able to show us your data?

[hedgddfgd]

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.

[Ksharp]

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;

[hedgddfgd]

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

[hedgddfgd]

And also the startID in col 1

[Ksharp]

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;

[hedgddfgd]

Thank you so much, that worked!

[hedgddfgd]

I'll try to learn more about IML.

[hedgddfgd]

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

[hedgddfgd]

My original data now Looks like this:

[Ksharp]

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;

[Ksharp]

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

[Ksharp]

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;

[KachiM]

@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: