BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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