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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.