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
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.
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;
Thanks a lot! But how can I Keep the names of the cols?
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;
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
My original data now Looks like this:
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;
If you add a diagonal of 0 , you would get one more variable ,how do you rename it ?
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;
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:
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!
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.
Ready to level-up your skills? Choose your own adventure.