Removing duplicates based on two variables, but prioritizing based on a third variable

[novinosrin]

A small test

data have;
input ID   index_date :mmddyy10.   priority;
format  index_date mmddyy10.;
cards;
1 2/3/18 0
1 2/3/18 1
1 2/3/18 0
2 3/4/15 0
3 7/14/14 0
3 7/14/14 0
4 10/1/17 1
;


proc sql;
create table want(drop=t) as
select distinct *,count(distinct priority)>1 as t
from have
group by ID ,  index_date
having t and priority=1 or t=0 ;
quit;

proc print noobs;run;

1 02/03/2018 1
2 03/04/2015 0
3 07/14/2014 0
4 10/01/2017 1

[Tom]

Your test data is too simple.  Just add one more variable, like a row number to see why.

row+1;

ID index_date priority row

1 02/03/2018 1 2
2 03/04/2015 0 4
3 07/14/2014 0 5
3 07/14/2014 0 6
4 10/01/2017 1 7

[novinosrin]

That's correct. The code assumes no more extra variables. 

[Tom]

If there aren't any extra variables then just let the DISTINCT keyword to the work.

proc sql;
create table want as
  select distinct *
  from have
  group by ID , index_date
  having priority=max(priority)
  order by ID , index_date
;
quit;

[novinosrin]

Excellent catch. Afternoon drowsiness. Kudos!