topic Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0 in SAS Programming

converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Sun, 11 Jun 2023 02:27:42 GMT

My data is a list of edges (links) of a social network. It has only two columns: the first represents the focus node ID and the second represents the target node ID. The order, however, is not important, as the network is undirected. The link repetition is not important as the network should be binary.

How can I create the adjacency matrix for an undirected network with elements equal to 1 if there is any number of links between node pairs, and zero otherwise?

(ultimately, I want to calculate the node centralities.)

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Ksharp — Sun, 11 Jun 2023 10:00:55 GMT

/*
You want this ?
If you have SAS/OR, the code could be more compact and less.
*/
data have;
infile cards ;
input from $  to $ ;
cards;
1     2
1     3
4     5
5     2
9     4
6     7
8     7
;
run;
data full;
  set have end=last;
  if _n_ eq 1 then do;
   declare hash h();
    h.definekey('node');
     h.definedata('node');
     h.definedone();
  end;
  output;
  node=from; h.replace();
  from=to; to=node;
  output;
  node=from; h.replace();
  if last then h.output(dataset:'node');
  drop node;
run;


data want(keep=node household);
declare hash ha(ordered:'a');
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('last');
ha.definedone();
declare hash _ha(hashexp: 20);
_ha.definekey('key');
_ha.definedone();

if 0 then set full;
declare hash from_to(dataset:'full(where=(from is not missing and to is not missing))',hashexp:20,multidata:'y');
 from_to.definekey('from');
 from_to.definedata('to');
 from_to.definedone();

if 0 then set node;
declare hash no(dataset:'node');
declare hiter hi_no('no');
 no.definekey('node');
 no.definedata('node');
 no.definedone();
 

do while(hi_no.next()=0);
 household+1; output;
 count=1;
 key=node;_ha.add();
 last=node;ha.add();
 rc=hi.first();
 do while(rc=0);
   from=last;rx=from_to.find();
   do while(rx=0);
     key=to;ry=_ha.check();
      if ry ne 0 then do;
       node=to;output;rr=no.remove(key:node);
       key=to;_ha.add();
       count+1;
       last=to;ha.add();
      end;
      rx=from_to.find_next();
   end;
   rc=hi.next();
end;
ha.clear();_ha.clear();
end;
stop;
run;


proc sql;
create table want2 as
select x.*,coalesce(y.v,0) as v
from 
(select * from (select distinct node as node1 from want),(select distinct node as node2 from want)) as x
natural left join
(select a.node as node1,b.node as node2,1 as v from want as a,want as b where a.household=b.household and a.node ne b.node) as y
order by 1,2;
quit;

proc transpose data=want2 out=final_want(drop=_name_);
by  node1;
var v;
id node2;
run;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Tom — Sun, 11 Jun 2023 14:48:05 GMT

If the nodes are identified by a series of integers (and if not you could always just sort and number them)

And the number of nodes is reasonable (and if not you are going to have a hard time doing calculations).

Then just make a 2-D array. You can then write the 2-D array out as a dataset.

Example:

data have;
  input from to ;
cards;
1  2
1  3
4  5
5  2
6  7
8  7
;

data matrix ;
  array x[8,8] _temporary_ (64*0);
  do until (eof);
    set have end=eof;
    x[from,to]=1;
    x[to,from]=1;
  end;
  do node=1 to 8;
    array nodes [8] node1-node8 ;
    do col=1 to 8;
      nodes[col]=x[node,col];
    end;
    output;
  end;
  keep node: ;
run;

Result

Obs    node    node1    node2    node3    node4    node5    node6    node7    node8

 1       1       0        1        1        0        0        0        0        0
 2       2       1        0        0        0        1        0        0        0
 3       3       1        0        0        0        0        0        0        0
 4       4       0        0        0        0        1        0        0        0
 5       5       0        1        0        1        0        0        0        0
 6       6       0        0        0        0        0        0        1        0
 7       7       0        0        0        0        0        1        0        1
 8       8       0        0        0        0        0        0        1        0

If you want the main diagonal to be 1's then add this statement after the ARRAY statement in the DO NODE loop.

    nodes[node,node]=1;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Tom — Sun, 11 Jun 2023 15:50:19 GMT

If you want to use HASH() objects (which is useful if the NODE ids are not contiguous integers) then you can build the connectivity data into a single HASH that can be written to a dataset and then transposed.

data _null_;
* Declare hashs and hiters ;
  declare hash nodes(ordered:'yes');
   nodes.definekey('node');
   nodes.definedata('node');
   nodes.definedone();
  declare hash links(ordered:'yes');
   links.definekey('from','to');
   links.definedata('from','to','x');
   links.definedone();
  declare hiter ifrom('nodes');
  declare hiter ito('nodes');
  x=1;
* read in the data and populate the hashes ;
  do until(eof);
    set have end=eof;
    links.replace(key:to,key:from,data:to,data:from,data: 1);
    links.replace(key:from,key:to,data:from,data:to,data: 1);
    node=from; nodes.replace();
    node=to; nodes.replace();
  end;
* Iterate over the list of nodes and fill in the zeros ;
* Set diagonal to ones ;
  rc1 = ifrom.first();
  do while (rc1 = 0);
    from = node;
    rc2 = ito.first();
    do while (rc2 = 0);
      to = node;
      rc3=links.add(key:from,key:to,data:from,data:to,data:(from=to));
      rc2 = ito.next();
    end;
    rc1 = ifrom.next();
  end;
* write out the links ;
  links.output(dataset:'links');
  stop;
run;

* Transpose ;
proc transpose data=links out=want(drop=_name_) prefix=to;
  by from;
  id to;
  var x;
run;

So if you have this input data:

data have;
  input from $ to $ ;
cards;
1  FRED
1  3
4  5
5  FRED
;

You get this result

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Sun, 11 Jun 2023 21:34:07 GMT

Thank you for the response! Attached is an example of the data, and I can't figure out the SAS code for the adjacency matrices. The node IDs are randomly assigned (unique) integers and are important for later merging the centrality scores with other data. Any help is greatly appreciated!

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Tom — Sun, 11 Jun 2023 22:25:23 GMT

Not allowed to download XLSX files so if you want to share example data please just post a data step that makes the dataset.

But the real question is what are the statistics you want to calculate?

Are you sure it is easier to calculate from a "matrix" instead of the data you already have?

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

PGStats — Sun, 11 Jun 2023 22:40:05 GMT

This page references 7 different measures of node centrality.

Which centrality measure do you want to calculate? And do you want to do the calculation with SAS or simply provide a well formatted file to other software?

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Sun, 11 Jun 2023 22:46:32 GMT

Ultimately, I want the centrality (eigenvector, degree, betweenness, closeness) scores for each node. I have done this in another software that takes only small data of ~100 nodes and it was easier to build the adj. matrix first and then calculate the centralities. Can that be skipped in SAS?

Node ID is a random integer (may as well be text) that's important for later merging with other data. Ex.:

data have; infile cards ;

input from $ to $ ;
cards;
1 3
1 5
3 5
3 6
15 18
21 37
28 53
;
run;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Sun, 11 Jun 2023 22:52:03 GMT

Ultimately, I want the centrality (eigenvector, degree, betweenness, closeness) scores for each node, in SAS... I don't actually care for the intermediate steps. It was my assumption that I needed the adj. matrix before calculating the scores

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Sun, 11 Jun 2023 22:54:03 GMT

I have ~5,000 nodes.

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Tom — Mon, 12 Jun 2023 00:09:41 GMT

@Max123 wrote:
Ultimately, I want the centrality (eigenvector, degree, betweenness, closeness) scores for each node, in SAS... I don't actually care for the intermediate steps. It was my assumption that I needed the adj. matrix before calculating the scores

And how to you propose to calculate those scores?

Do you know the algorithm needed?

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Mon, 12 Jun 2023 00:20:12 GMT

found this example online:
proc netflow in=network out=degree_centrality action=stats;
stats degree / type=degree;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Ksharp — Mon, 12 Jun 2023 11:45:12 GMT

Tom,
I think it is all depend on what OP are looking for.
We understand it in two different way.
According to your logistic , OP's question is very very simple .
I think OP should post an example to show his input dataset and output dataset.

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Ksharp — Mon, 12 Jun 2023 11:47:16 GMT

/*
I think you should post an example to show your input dataset and output dataset.
Tom and I understand it in two different way.
The following code is according to Tom's logistic.
*/
proc import datafile='c:\temp\book1.xlsx' out=_2005 dbms=xlsx replace;
sheet='2005';
run;
data have;
 set _2005;
 v=1;
 output;
 temp=node_ID; node_ID=linked_node_ID;linked_node_ID=temp;output;
 drop temp;
run;
proc sql;
create table want as
select a.*,coalesce(b.v,0) as v
 from  (
select * from
(select  node_ID as node_ID        from have union select linked_node_ID from have),
(select  node_ID as linked_node_ID from have union select linked_node_ID from have)
) as a natural left join (select distinct * from have) as b;
quit;
proc transpose data=want out=final_want(drop=_name_);
by node_ID;
var v;
id linked_node_ID;
run;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

PGStats — Mon, 12 Jun 2023 20:16:22 GMT

These network (graph) statistics are now available from PROC OPTGRAPH, for which you need the SAS Network Algorithms license.

From the procedure documentation, you only need a links (i.e. from-to pairs) dataset to get it going.

The centrality statistics are requested with the CENTRALITY statement.

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Mon, 12 Jun 2023 22:03:04 GMT

Thank you both so much!!!

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Tue, 13 Jun 2023 01:29:30 GMT

unfortunately, I don't have that license. and PROC NETFLOW is not working...

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Tue, 13 Jun 2023 03:47:30 GMT

sorry to build on this... How can I multiply element-wise to adjacency matrices (e.g., final_want2005 and final_want2006)?
I'm trying this but it doesn't work:

proc iml ; reset noprint ;
A = final_want2005[1:2,4710:4711] ;
B = final_want2006[1:2,4710:4711] ; c = A # B ;
run ;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Ksharp — Tue, 13 Jun 2023 12:19:45 GMT

This is a new question, should post it at IML forum.

I check your LOG.

56
57   A = final_want2005[1:2,4710:4711] ;
58   B = final_want2006[1:2,4710:4711] ;
ERROR: (execution) Invalid subscript or subscript out of range.

 operation : [ at line 58 column 19
 operands  : final_want2006, _TEM1001, *LIT1007, *LIT1008
final_want2006   4694 rows   4695 cols    (numeric)

_TEM1001      1 row       2 cols    (numeric)

final_want2006 only have 4695 columns unlike final_want2005 have 4711 columns, so you can't do this element-wise operator.

Here is my running code:

proc iml;
use final_want2005;
read all var _num_ into final_want2005;
close;
use final_want2006;
read all var _num_ into final_want2006;
close;

/*
x=ncol(final_want2005);
y=ncol(final_want2006);
print x y;
quit;
*/
A = final_want2005[1:2,4710:4711] ;
B = final_want2006[1:2,4710:4711] ; 
c = A # B ;
quit;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

Max123 — Tue, 13 Jun 2023 12:37:12 GMT

Thanks. The data was not complete for 2006 in the previous file - actually both matrices have the same dimensions given full data. The error it gave me from my code was about matrices not having assigned value.