topic Re: create unique id for duplicates referencing the same customer in Mathematical Optimization, Discrete-Event Simulation, and OR

create unique id for duplicates referencing the same customer

majdi_ka — Mon, 26 Oct 2015 09:53:04 GMT

Hello,

I'm working on a data quality problem, and more specifically detecting duplicates with fuzzy logic using COMPGED and COMPLEV functions based on this paper.

I have detected duplicates, but per couple.
example:
assuming that customers with customer_id equals 01, 02 and 03 reference the same customer (let's call him John), my SAS program detects it this way:

row_num | duplicate_id
1 | 01-02
2 | 01-03
3 | 02-03

where duplicate_id is an id that I've created combining both customer ids to reference a couple of duplicates.

So what I want now is to have a unique id for John !

The final result that I want is :
duplicate_id | unique_id
01-02 | 01
01-03 | 01
02-03 | 01

I had the idea to use undirected graph (Operational Research algorithm) but I'm not sure I have the licence for it, and I'm not sure if I can associate a new id even if it works.

the first example in this paper that uses hash objects can be a first step to find the solution, but the expected result is not what I want.

Any ideas? Thanks!

Re: create unique id for duplicates referencing the same customer

LinusH — Mon, 26 Oct 2015 19:05:59 GMT

"(Operational Research algorithm) but I'm not sure I have the licence for it"

Submit

PROC SETINIT;

RUN;

You should see a line a log that reads SAS/OR, otherwise, you haven't it licensed.

Re: create unique id for duplicates referencing the same customer

PGStats — Mon, 26 Oct 2015 16:02:38 GMT

If you don't have the SAS/OR licence, check out this wonderful article about a way to find connected components without SAS/OR:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-find-all-connected-components-in-a-graph/ta-p/231539

Re: create unique id for duplicates referencing the same customer

ndp — Mon, 26 Oct 2015 19:49:26 GMT

I don't know about SAS/OR but if you can create a dataset of pairs you can do this in base SAS

data pair;
input pair $;
datalines;
01-02
01-03
02-03
04-05
04-06
05-06
;
run;

data new(drop=pair1 pair2);
	set pair;
	retain unique pair1 pair2;
	if _n_=1 then do; unique=1; pair1=scan(pair,1,"-"); pair2=scan(pair,2,"-"); end;
	else if indexw(translate(pair," ","-"),pair1)<=0 and indexw(translate(pair," ","-"),pair2)<=0 then do; unique=unique+1; pair1=scan(pair,1,"-"); pair2=scan(pair,2,"-"); end;
run;

Re: create unique id for duplicates referencing the same customer

majdi_ka — Tue, 27 Oct 2015 09:32:58 GMT

Thanks for your answer.

I checked and I don't have licence for it..

Re: create unique id for duplicates referencing the same customer

majdi_ka — Tue, 27 Oct 2015 10:58:07 GMT

Thank you @PGStats for the great article !

I 've tried the example in the article, and it worked ! (there's a "&colon;" to replace with ":" in the macro SubGraphs)
I've tried it also on @ndp's example, adding other ids and it worked !

Thanks a lot !

Re: create unique id for duplicates referencing the same customer

majdi_ka — Tue, 27 Oct 2015 11:11:03 GMT

Thanks @ndp for your answer,

It worked for your example, but when adding another observation 07-01 to the data I don't get the same unique id as the others.

it would work if I added 01-07 instead, and then sorted the table.

I didn't verify the program with all the prequisites, but it's still a good solution.

Thanks a lot !

Re: create unique id for duplicates referencing the same customer

PGStats — Tue, 27 Oct 2015 15:54:34 GMT

Thank you for finding the &colon; problem. I revised the article. Cheers!