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.
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
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
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
Thanks very much PG!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.