BookmarkSubscribeRSS Feed
hedgddfgd
Calcite | Level 5

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

17 REPLIES 17
PeterClemmensen
Tourmaline | Level 20

@hedgddfgd Hi and welcome to the SAS Community 🙂

 

Are you able to show us your data?

hedgddfgd
Calcite | Level 5

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.Unbenannt.JPG

Ksharp
Super User

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
Calcite | Level 5

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

hedgddfgd
Calcite | Level 5
And also the startID in col 1
Ksharp
Super User

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
Calcite | Level 5
Thank you so much, that worked!
hedgddfgd
Calcite | Level 5
I'll try to learn more about IML.
hedgddfgd
Calcite | Level 5

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
Calcite | Level 5

My original data now Looks like this:Unbenannt (1).JPG

Ksharp
Super User

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
Super User

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

Ksharp
Super User

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
Rhodochrosite | Level 12

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

 

Capture_01.JPG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 17 replies
  • 2459 views
  • 4 likes
  • 5 in conversation