topic Sort and delete ALL duplicated obs in SAS Programming

Sort and delete ALL duplicated obs

iank131 — Sun, 24 Mar 2013 12:48:24 GMT

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.

Re: Sort and delete ALL duplicated obs

PGStats — Sun, 24 Mar 2013 13:22:00 GMT

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;

Re: Sort and delete ALL duplicated obs

PGStats — Sun, 24 Mar 2013 13:28:12 GMT

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;

Re: Sort and delete ALL duplicated obs

iank131 — Sun, 24 Mar 2013 14:50:39 GMT

Thanks very much PG!