topic Re: 6-ways of removing duplicate in SAS Programming

6-ways of removing duplicate

AnandSahu — Tue, 02 Jul 2013 09:10:57 GMT

Hi All,

Please help me to find out 5-6 ways of removing duplicates from a dataset.

Regards

Andy

Re: 6-ways of removing duplicate

kuridisanjeev — Tue, 02 Jul 2013 10:31:37 GMT

Hi..

Normally we use bellow method's to remove duplicate observations from the dataset.

1.Nodup(Proc Sort)

2.nodupkey(Proc Sort)

3.Disticnt(Proc Sql )

4.First. and last . variables(In Datastep)

Regards.

Sanjeev.K

Re: 6-ways of removing duplicate

Ksharp — Tue, 02 Jul 2013 10:36:05 GMT

5. proc freq ;

6.proc means;

7.proc summary;

8.HashTable

9. proc tabulate

10.proc report

Re: 6-ways of removing duplicate

kuridisanjeev — Tue, 02 Jul 2013 10:54:02 GMT

Hi ..

Yes i do agree with your Additional ways to remove duplicates.

Is there any way to remove duplicates in Proc Sql with NODUP KAY functionality ?? as Distinct Works similar to NODUP.

Regards.

Sanjeev.K

Re: 6-ways of removing duplicate

Ksharp — Tue, 02 Jul 2013 11:20:36 GMT

What is that mean ?

select distinct id,name ?

select distinct cats(id,name) ?

proc sql;

select distinct sex,age from sashelp.class;

quit;

Message was edited by: xia keshan

Re: 6-ways of removing duplicate

kuridisanjeev — Tue, 02 Jul 2013 11:43:00 GMT

Hi..

I will try to elaborate my question with bellow example .

If Dataset looks like this.

ID Fee

100 2000

100 3000

101 4000

101 3200

If we use

Proc Sql;

Select Distinct ID,Fee from XX;

quit;

(OR)

Proc sort data=XX nodup;

by ID;

run;

above code will display all the data.because fee is different with in the same ID.

but in the case of

Proc Sort Data=XX nodupkey;

by ID;

run;

above code will generate only 2 rows (100 and 101).

How can we achieve the same by using Proc SQL??

Regards.

Sanjeev.K

Re: 6-ways of removing duplicate

Ksharp — Tue, 02 Jul 2013 11:56:39 GMT

select Distinct ID,Fee from XX group by id having fee=max(fee) ;

Re: 6-ways of removing duplicate

umashankersaini — Tue, 02 Jul 2013 18:57:58 GMT

Hi Andy,

I may not be much familiar with all ways but trying my best.....

(1) proc sort data=DSN noduprecs;

by _all_ ; Run;

(2) Proc sort data=DSN out=sample nodupkey dupout=Duplicate;

by var1; Run;

(3) Proc Sql noprint;

create table unique as select distinct (*) from DSN;

quit;

(4) proc freq data=DSN noprint;

tables variable/out=unique (Keep=variable count where=(count=1));

Run;

(5) Proc sort data=DSN out=temp;

by variable ; Run;

Data Unique;

set temp;

by variable;

if not first.variable and last.variable;

run;

Re: 6-ways of removing duplicate

AnandSahu — Tue, 02 Jul 2013 18:59:33 GMT

thanx uma...

Is there any way to remove duplicateS with the help of PROC APPEND and MERG as well?????

Re: 6-ways of removing duplicate

sunilzood — Wed, 03 Jul 2013 02:24:03 GMT

Anand, to use merge & append, you need to sort the observation & while sorting Nodup key take cares of dup records!!!

Re: 6-ways of removing duplicate

Ksharp — Wed, 03 Jul 2013 02:57:51 GMT

(5) is wrong.

Re: 6-ways of removing duplicate

PGStats — Wed, 03 Jul 2013 03:20:41 GMT

Should be:

(5) Proc sort data=DSN out=temp;

by variable ; Run;

Data Unique;

set temp;

by variable;

if first.variable;

run;

Re: 6-ways of removing duplicate

Ksharp — Wed, 03 Jul 2013 04:35:10 GMT

Absolutely

Re: 6-ways of removing duplicate

AnandSahu — Wed, 03 Jul 2013 05:39:05 GMT

Thanx PG State for providing a ans.

Re: 6-ways of removing duplicate

umashankersaini — Wed, 03 Jul 2013 05:53:24 GMT

Apologies for 5th one....Thanks for correcting that....

Re: 6-ways of removing duplicate

CharlotteCain — Wed, 03 Jul 2013 09:23:30 GMT

Hi Ksharp,

I would like to know different ways of identifying duplicates or in other words subsetting just the duplicates into a new dataset, perhaps I should have posted a new question however I thought I would add here as I noticed you and PG Sir got involved in the thread, got me excited, also others can learn too.

Cheers from England,

Charlotte

P.S I have a feeling, key= option/unique in set statement can perhaps be used for remving duplicates on a self merge?

Re: 6-ways of removing duplicate

kuridisanjeev — Wed, 03 Jul 2013 09:40:03 GMT

HI CharlotteCain

You want to create a new dataset with all duplicates observations ???

If yes, here is the way..

Proc sort data=DSN out=sample nodupkey dupout=Duplicate;

by var1;

Run;

A new dataset duplicate will be created and it contains all duplicate observations from DSN dataset.

You can achive the same by using Data step as well.

Proc sort data=DSN ;

by var1;

Run;

Data Unique Duplicates;

set DSN;

by Var1;

If First.Var1 then Output Unique;

else output Duplicates;

run;

Hope this helps.

Regards.

Sanjeev.K

Re: 6-ways of removing duplicate

kuridisanjeev — Wed, 03 Jul 2013 09:53:04 GMT

Hi Ksharp..

Its looks fine only when i have two variables in the dataset.but what in the case of dataset set having 10 or more variables and still i want to remove observation based on one variable in Proc SQL ???

Regards.

Sanjeev.K

Re: 6-ways of removing duplicate

Ksharp — Wed, 03 Jul 2013 10:26:30 GMT

Just list it in SELECT clause.

select id,free,xx,xx,.......

Re: 6-ways of removing duplicate

kuridisanjeev — Wed, 03 Jul 2013 10:31:15 GMT

You mean

Select Distinct Id,fee,xx,xx,xx,xx,,,,,?????

Will It remove Duplicates based on ID variable? Not sure..

Regards.

Sanjeev.K