Hi @wylie_ma Keeping it simple albeit I acknowledge is boring-
data have;
infile cards dsd ;
input Phone :$10. Account $ ;
cards;
123456789, 12345
123456789, 54321
123456789, 67890
123456789, 09876
987654321, 12345
987654321, 54321
987654321, 45879
987654321, 94532
987654321, 67890
543219876, 45879
543219876, 54873
543219876, 56454
543219876, 87654
;
/*All pairs*/
data temp/view=temp;
merge have(rename=(phone=phone1 account=account1)) have(rename=(phone=phone2 account=account2));
run;
proc freq data=temp noprint;
tables phone1*phone2/ sparse out=temp2(where=(phone1 ne phone2) keep=phone:);
run;
/* Compute common*/
proc sql;
create table common as
select a.*,ifn(common=.,0,common) as common
from
(select * from temp2) a
left join
(select a.phone as phone1,b.phone as phone2,sum(a.account= b.account) as common
from have a, have b
where a.account=b.account and a.phone ne b.phone
group by phone1,phone2) b
on a.phone1=b.phone1 and a.phone2=b.phone2;
quit;
/*get the Phone pairs order to remove dups in the next step*/
data _common;
set common;
call sortc(phone1,phone2);
run;
/*Final step*/
proc sort data=_common out=want nodupkey;
by phone1 phone2;
run;
proc print noobs;run;
phone1
phone2
common
123456789
543219876
0
123456789
987654321
3
543219876
987654321
1
... View more