topic Re: How to keep one observation by group (deleting duplicates by condition) in SAS Programming

How to keep one observation by group (deleting duplicates by condition)

cphd — Mon, 20 Jan 2020 18:08:35 GMT

Hi all,

I have a dataset looks like below. I want to keep the observations with the highest number in "count" variable by county and year.

County year Count

20001 2008 1

20001 2008 2

20001 2008 3

20001 2006 1

20002 2008 1

20002 2008 2

The outcome that I want has to look like...

County year Count

20001 2008 3

20001 2006 1

20002 2008 2

proc sort data=rc out=rc_dup nodupkey;
	by county year;
run;

Re: How to keep one observation by group (deleting duplicates by condition)

Kurt_Bremser — Mon, 20 Jan 2020 18:12:45 GMT

proc sort data=have;
by county year count;
run;

data want;
set have;
by county year;
if last.year;
run;

Re: How to keep one observation by group (deleting duplicates by condition)

novinosrin — Mon, 20 Jan 2020 18:53:38 GMT

1. SORT approach would require 2 sorts:

data have;
input County       year       Count;
cards;
20001       2008           1
20001       2008           2
20001       2008           3
20001       2006           1
20002       2008           1
20002       2008           2
;

proc sort data=have out=temp;
by county year descending count;
run;

proc sort data=temp out=want nodupkey;
by county year;
run;

data have;
input County       year       Count;
cards;
20001       2008           1
20001       2008           2
20001       2008           3
20001       2006           1
20002       2008           1
20002       2008           2
;

data want;
 set have;
 by county year notsorted;
 if last.year;
run;

Assuming your dataset order is what it is as shown in your sample, you would need a NOTSORTED option in the BY statement in your datastep

3. Proc SQL grouped filter using HAVING clause


proc sql;
create table want as
select *
from have
group by county,year
having count=max(count);
quit;