topic Re: Deleting duplicate rows in SAS Data Management

Deleting duplicate rows

ilikesas — Thu, 18 Dec 2014 01:19:22 GMT

Hi,

suppose I have the following table:

ID	Name
1	Mike
1	Mike
2	George
3	Jack
3	Jack
4	Tan

Is it possible to delete the duplicate rows for IDs 1 and 3, but the rows where there are no duplicates, like those for IDs 2 and 4, to keep them as they are?

Thank you,

Re: Deleting duplicate rows

stat_sas — Thu, 18 Dec 2014 01:34:22 GMT

data want;

set have;

by id;

if first.id;

run;

Re: Deleting duplicate rows

ilikesas — Thu, 18 Dec 2014 02:03:40 GMT

Hi stat@sas,

I did the following including your code:

data dup;

input id name$;

datalines;

3 a

1 d

5 e

4 y

2 t

;

run;

data dup2;

set dup;

by id;

if first.id;

run;

But I get an error message:

ERROR 180-322: BY variables not properly sorted on dataset DUP

And the result that I get is:

	id	name
1	3	a

It seems that the duplicate was deleted for the first row, but after that the code stopped functioning

Thank you

Re: Deleting duplicate rows

stat_sas — Thu, 18 Dec 2014 02:08:32 GMT

You need to sort dataset dup by id before performing by processing

proc sort data=dup;

by id;

run;

then try this

data dup2;

set dup;

by id;

if first.id;

run;

Re: Deleting duplicate rows

Haikuo — Thu, 18 Dec 2014 02:15:16 GMT

In this case, who not just:

proc sort data=dup nodupkey;

by id;

run;

Re: Deleting duplicate rows

stat_sas — Thu, 18 Dec 2014 02:22:51 GMT

Thanks Haikuo - Yes, this is a better solution.

Re: Deleting duplicate rows

ilikesas — Thu, 18 Dec 2014 02:42:45 GMT

Thank you Hai.kuo and stat@sas !!!