Solved
Contributor
Posts: 53

# Sort and delete ALL duplicated obs

Is there an easy one (PROC or DATA) step method of sorting and deleting ALL observations that have duplicate by variables INCLUDING the first occurrence of the duplicate? I can do this with the following code, but is there an elegant one step way of doing this? May be with PROC SQL? The following code removes four obs from the "dirty" data set (2 obs have duplicate BY variables hence 2*2=4), leaving only three obs in the final "cleaned" data set.

data dirty;

input var1 var2 x;

cards;

1 1 4

1 2 5

1 3 6

1 1 5

2 2 5

2 4 6

2 2 1

; run;

proc sort data=dirty out=cleaner dupout=dirt nodupkey;

by var1 var2; run;

data cleaned; merge cleaner dirt (keep=var1 var2 in=del);

by var1 var2;

if del then delete; run;

Thanks

Ian.

Accepted Solutions
Solution
‎03-24-2013 09:28 AM
Posts: 5,541

## Re: Sort and delete ALL duplicated obs

And for a SQL solution :

data dirty;
input var1 var2 x;
cards;
1 1 4
1 2 5
1 3 6
1 1 5
2 2 5
2 4 6
2 2 1
;

proc sql;
create table clean as
select * from dirty
group by var1, var2
having count(*)=1;
quit;

PG

PG

All Replies
Posts: 5,541

## Re: Sort and delete ALL duplicated obs

A simpler non-sql solution :

data dirty;
input var1 var2 x;
cards;
1 1 4
1 2 5
1 3 6
1 1 5
2 2 5
2 4 6
2 2 1
;

proc sort data=dirty; by var1 var2; run;

data clean;
set dirty;
by var1 var2;
if first.var2 and last.var2;
run;

PG

PG
Solution
‎03-24-2013 09:28 AM
Posts: 5,541

## Re: Sort and delete ALL duplicated obs

And for a SQL solution :

data dirty;
input var1 var2 x;
cards;
1 1 4
1 2 5
1 3 6
1 1 5
2 2 5
2 4 6
2 2 1
;

proc sql;
create table clean as
select * from dirty
group by var1, var2
having count(*)=1;
quit;

PG

PG
Contributor
Posts: 53

## Re: Sort and delete ALL duplicated obs

Thanks very much PG!

🔒 This topic is solved and locked.