Find all customers that are related each other

Ronein — Thu, 20 Oct 2022 05:57:27 GMT

Hello

Lets say that there is a data set called "Have" that contain 2 columns : IP (identity number ) and customer ID.

As you can see specific customer can have multiple owners (Multiple IP's ).

Specific IP can be belonged to multiple customers (Multiple customer ID that IP belong to ).

My target:

I want to know for each customer_ID what are the other customer_ID's that are related.

For example:

Under customer_ID 7777777 there are 3 IP's (1,2,3)

IP 1 belong also to customer_ID 8888888

IP 3 belong also to customer_ID 9999999

So the conclusion is that customer_ID 77777777 is related to customer_ID's 88888888,99999999

What is the way to do it please?

Data have;
Input IP customer_ID ;
cards;
1 7777777
1 8888888
2 7777777
3 7777777
3 9999999
;
Run;
proc sort data=have;by IP ;Run;
proc transpose data=have out=have2(drop=_NAME_) prefix=customer_ID;
by IP ;
var customer_ID;
run;


Data want1;
Input customer_ID other_customer_ID1 other_customer_ID2;
cards;
7777777 8888888 9999999
8888888 7777777
9999999 7777777
;
Run;


Data want2;
Input customer_ID other_customer;
cards;
7777777 8888888 
7777777 9999999
8888888 7777777
9999999 7777777
;
Run;

Re: Find all customers that are related each other

Ksharp — Thu, 20 Oct 2022 11:40:11 GMT


data have;
infile cards ;
input from $  to $ ;
cards;
1 7777777
1 8888888
2 7777777
3 7777777
3 9999999
;
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;

topic Find all customers that are related each other in SAS Programming

Find all customers that are related each other

Re: Find all customers that are related each other