05-30-2017 05:11 AM
i need help. I have a dataset, sample below:
want to output customers with same mobile number but different names. in the data above we can see that Sam and Sam+Joe have the same number, this means Sam is Joe's partner so they can have the same number. but John and Tim are not connected and have the same number, those are the guys i want to output
thanks in advance
05-30-2017 05:44 AM
Post test data in the form of a datastep.
Maybe something like (and can't test as not test data):
proc sql; create table WANT as select distinct MOBILE_NUMBER, CUSTOMER_NAME from HAVE group by MOBILE_NUMBER, CUSTOMER_NAME having count(*) gt 1; quit;
05-30-2017 06:44 AM - edited 05-30-2017 06:46 AM
Names are often spelled differently, when entered more than once. As a result, I suggest you go about this as a two-step problem. First, find all the differences:
create table differences as
select a.*, b.customer_name as matching_name
from have a, have b
where a.mobile_number = b.mobile_number
and a.customer_name ne b.customer_name;
if length(customer_name) le length(matching_name) then do;
dummy = customer_name;
customer_name = matching_name;
matching_name = dummy;
In DIFFERENCES2, CUSTOMER_NAME and MATCHING_NAME will be different, but the DATA step guarantees that CUSTOMER_NAME will be the variation that contains more characters.
Then you can begin to automate the process of removing matches that should be removed. Due to variations in spelling, you may need more than one pass through the data. For example, does "Sam" match "Samuel"? Should those be output? Here is one pass through to eliminate the duplicates:
if index(customer_name, strip(matching_name)) = 0;
Instead of the INDEX function, you may want to use FINDW. It really depends on the nature of the data, as to which function gives you better results.