BookmarkSubscribeRSS Feed
Ronein
Onyx | Level 15

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;
1 REPLY 1
Ksharp
Super User

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 407 views
  • 0 likes
  • 2 in conversation