topic Re: Finding Duplicates in SAS Programming

Finding Duplicates

PRudnick — Mon, 28 Apr 2014 14:34:48 GMT

I am trying to find duplicates in one column that have different values in another. For example, different sender names with the same sender address. I am using EG and I am not sure which option would be the best.

For example:

Sender Name Sender Address

John Doe 123 Main St.

Jane Doe 123 Main St.

Thanks for the help!

Re: Finding Duplicates

RW9 — Mon, 28 Apr 2014 14:41:30 GMT

Can you use proc freq on the data?

Re: Finding Duplicates

Haikuo — Mon, 28 Apr 2014 14:52:44 GMT

if you don't mind putting down some SAS code, and assuming name and address are all you have, then try the following:

proc sql;

create table want/*this is your output table*/ as

select * from have /*this is the input table*/

group by sender_address

having count(*) >1

;quit;

Or like suggested, try using proc freq. In EG, you will find it under the task name called "One-Way Frequencies".

Haikuo

Re: Finding Duplicates

PRudnick — Mon, 28 Apr 2014 15:31:42 GMT

There is a lot of data involved.... I have 49 different columns. Proc freq will show me how many times an address is used but I want to be able to output the instance where different Senders are using the same address

Re: Finding Duplicates

Haikuo — Mon, 28 Apr 2014 16:00:30 GMT

You could try using query builder , and/or one-way frequencies, or just run the code that I posted with a minor weak:

proc sql;

create table want/*this is your output table*/ as

select * from have /*this is the input table*/

group by sender_address

having count(distinct sender_name) >1

;quit;

This will give you all the addresses that have more than one different names.

Haikuo

Update: not that I am not aware you are EG user, but to post numerous screen shots is just too much for me.

Re: Finding Duplicates

PravinMishra — Mon, 04 Aug 2014 17:35:16 GMT

Hi,

If you have Data Flux Tool you can do it very easily. you need to run the DF job with proper QKB attached with it.

It will generate same ID for duplicate records.

Let me know if you want if you want more elaboration.

Thanks

Pravin