Solved
Contributor
Posts: 62

# Convert an edge list to an adjacency matrice

Hello,

I have an edge list that looks like this:

 LenderID LenderID2 counts 3606 1674 2 5848 1252 10 5848 1674 2 5848 2675 2

LenderID and LenderID2 are identication numbers. The number 2 has been added to differentiate the two variables in SAS. The variable ''counts'' is the number of loans the two lenders share together.

I search how to build two kinds of adjacency matrices.

The first would looks like this:

 1252 1674 2675 3606 5848 1252 0 0 0 0 1 1674 0 0 0 1 1 2675 0 0 0 0 1 3606 0 1 0 0 0 5848 1 1 1 0 0

It is a binary (unweighted) matrice; when two lenders share at least a loan together, it takes the number 1, otherwise 0.

The second matrice looks like this:

 1252 1674 2675 3606 5848 1252 0 0 0 0 10 1674 0 0 0 2 2 2675 0 0 0 0 2 3606 0 2 0 0 0 5848 10 2 2 0 0

It is a weighted matrice.

I've read that it can be possible with proc tabulate. Can you help me please?

Thank you very much in advance.

Accepted Solutions
Solution
‎09-06-2014 10:34 PM
Posts: 5,543

## Re: Convert an edge list to an adjacency matrice

It can be done by combining SQL and TRANSPOSE, this way:

data have;

input

ID1 ID2 count;

datalines;

3606 1674 2

5848 1252 10

5848 1674 2

5848 2675 2

;

proc sql;

create table nodes as

select id1 as id from have

union select id2 from have;

create table temp as

select

a.id as id1,

b.id as id2,

not missing(count) as n,

coalesce(c.count, 0) as count

from

nodes as a cross join

nodes as b left join

have as c on (a.id=c.id1 and b.id=c.id2) or

(a.id=c.id2 and b.id=c.id1)

order by a.id, b.id;

quit;

proc transpose data=temp out=binary(drop=_name_) prefix=_;

by id1;

id id2; idlabel id2;

var n;

run;

proc print data=binary noobs label; run;

proc transpose data=temp out=weighted(drop=_name_) prefix=_;

by id1;

id id2; idlabel id2;

var count;

run;

proc print data=weighted noobs label; run;

PG

PG

All Replies
Posts: 1,147

## Re: Convert an edge list to an adjacency matrice

if you use flag variable in the var statement of proc transpose you get the firt output, else if counts is used you get the second output.

data have;

input LenderID     LenderID2  counts;

if counts ne . then flag=1;

cards;

3606     1674      2

5848     1252      10

5848     1674      2

5848     2675      2

;

run;

proc sort data=have;

by lenderid;

run;

proc transpose data=have out=trans(drop=_name_) prefix=_;

by lenderid;

id lenderid2;

var flag;

run;

Thanks,

Jag

Thanks,
Jag
Solution
‎09-06-2014 10:34 PM
Posts: 5,543

## Re: Convert an edge list to an adjacency matrice

It can be done by combining SQL and TRANSPOSE, this way:

data have;

input

ID1 ID2 count;

datalines;

3606 1674 2

5848 1252 10

5848 1674 2

5848 2675 2

;

proc sql;

create table nodes as

select id1 as id from have

union select id2 from have;

create table temp as

select

a.id as id1,

b.id as id2,

not missing(count) as n,

coalesce(c.count, 0) as count

from

nodes as a cross join

nodes as b left join

have as c on (a.id=c.id1 and b.id=c.id2) or

(a.id=c.id2 and b.id=c.id1)

order by a.id, b.id;

quit;

proc transpose data=temp out=binary(drop=_name_) prefix=_;

by id1;

id id2; idlabel id2;

var n;

run;

proc print data=binary noobs label; run;

proc transpose data=temp out=weighted(drop=_name_) prefix=_;

by id1;

id id2; idlabel id2;

var count;

run;

proc print data=weighted noobs label; run;

PG

PG
Posts: 4,742

## Re: Convert an edge list to an adjacency matrice

If you're just after a report then below code using Proc Tabulate should work.

If you need the data also in a table for further processing then something along the line has posted would be the way to go because the output object of Proc Tabulate doesn't contain the "non-links" but they get only created when rendering the reports (the cells with '0' in it).

data have;

input LenderID LenderID2 counts;

datalines;

3606 1674 2

5848 1252 10

5848 1674 2

5848 2675 2

;

run;

data v_have_both_directions / view=v_have_both_directions;

set

have

have(rename=(LenderID=LenderID2 LenderID2=LenderID));

run;

ods output Table=Test;

options missing='0';

proc tabulate data=v_have_both_directions noseps ;

class LenderID LenderID2;

var counts;

title 'unweighted matrice';

table LenderID, LenderID2*n=' ';

title 'weighted matrice';

table LenderID, LenderID2*counts=' '*sum=' '*format=16.0;

run;

proc print data=test;

title 'Tabulate Output Object';

run;

Contributor
Posts: 62

## Re: Convert an edge list to an adjacency matrice

It works! Thank you to all!

🔒 This topic is solved and locked.