CODEWITHSUNDEEP
sqlsas
Rads
Rads

Reputation: 355

Create adjacency matrix from dataset in SAS

I have been trying desperately to create an adjacency matrix from a dataset (I have the equivalent in R), but am unable to do so in SAS (beginner proficiency). It would be very helpful if you could help me out with this. Also, kindly suggest if this and sparse matrices are possible in SAS (without SNA) ?

data test;
input id id_o;
cards;
100 300
600 400
200 300
100 200
;
run;

I find the union of all unique id and id_o to create a list

proc sql;
create table test2 as
select distinct id from
  (select id as id from test
   union all
   select id_o as id from test);
quit;

Test2 looks like

100 600 200 300 400

Now I want an adjacency matrix which assigns a 1 at a position when there is a link between Test2 (100 and id_o (300) from original dataset). Consider Test2 to be the i's and there is a 1 at a corresponding j.

So, the adjacency matrix will look like

    100 600 200 300 400
100  0   0   1   1  0
600  0   0   0   0  1
200  0   0   0   1  0
300  0   0   0   0  0
400  0   0   0   0  0

Upvotes: 0

Views: 778

Answers (1)

Reeza
Reeza

Reputation: 21264

Here's one way, expanding on your current code. First you need to create a empty table with all options and then fill in the 1/0's. Second transpose the table to the desired format. There may be a way to do this with proc distance or some other proc, but I'm not sure. 

*get all the 1's for matrix;
proc freq data=test;
table id*id_o/sparse out=dist1;
run;

*Fill into matrix with all options;
proc sql;
create table test3 as
select a.id, b.id as id_o, coalesce(c.count, 0) as count
from test2 as a
cross join test2 as b
left join dist1 as c
on a.id=c.id
and b.id=c.id_o
order by a.id, b.id;
quit;

*Transpose to desired format.
proc transpose data=test3 out=test4 prefix=id_;
by id;
id id_o;
var count;
run;

Upvotes: 1

Related Questions