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

[cphd]

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;

[Kurt_Bremser · Accepted solution]

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

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

[novinosrin]

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;

2.

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;