Try this: data my_table;
input ID $ Date :ddmmyy10. ;
format Date ddmmyy10.;
datalines;
1 01/01/1975
1 01/01/1990
1 01/01/2010
2 01/01/1998
3 01/01/2011
3 01/01/1989
4 01/01/2020
5 01/01/2018
;
run;
data other_table;
input ID $;
datalines;
2
3
4
5
;
run;
/* determine the oldest date, by ID */
proc sql;
create table oldest_date_by_id as
select ID
,min(date) as oldest_date format ddmmyy10.
from my_table
group by ID
;
quit;
/* FLAG = Y if the record has the oldest date by ID, and where the ID is not in OTHER_TABLE; otherwise, FLAG = N */
proc sql;
create table my_table_with_flag as
select a.ID
,a.Date
,case
when (a.Date = b.oldest_date and a.ID ne c.ID)
then 'Y'
else 'N'
end as flag
from my_table a
left join oldest_date_by_id b
on a.ID = b.ID
and a.Date = b.oldest_date
left join other_table c
on a.ID = c.ID
;
quit;
... View more