Convert an edge list to an adjacency matrice

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

Convert an edge list to an adjacency matrice

Hello,

I have an edge list that looks like this:

LenderIDLenderID2counts
360616742
5848125210
584816742
584826752

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:

12521674267536065848
125200001
167400011
267500001
360601000
584811100

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:

12521674267536065848
1252000010
167400022
267500002
360602000
5848102200

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
Respected Advisor
Posts: 4,820

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: Convert an edge list to an adjacency matrice

Please try porc transpose

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
Respected Advisor
Posts: 4,820

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
Respected Advisor
Posts: 4,137

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 680 views
  • 6 likes
  • 4 in conversation