Sort and delete ALL duplicated obs

[iank131]

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.

[PGStats]

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

[PGStats · Accepted solution]

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

[iank131]

Thanks very much PG!