First choose the max(date_B) and find the min(date_A) > max(date_B) and WHERE grade = '.' (Which means 111111 29/12/2014 . should affect) (Which means 100000 30/12/2019 . should affect) are you sure this statement is true for ID 111111 results, would record dated 4-5-2014 be less than 12-29-2014? I believe this code does what you are asking for correctly. data Master;
input ID dt : ddmmyy10. grade;
cards;
111111 02/07/2013 1
111111 02/04/2014 1
111111 29/12/2014 .
111111 05/04/2014 .
100000 01/10/2012 .
100000 08/10/2015 .
100000 10/01/2017 5
100000 29/05/2018 1
100000 30/12/2019 .
111122 05/01/1922 .
111122 07/12/1932 .
111122 05/01/1933 .
111122 05/01/1935 .
111122 05/01/2018 1
111122 05/02/2018 .
;
run;
proc sort data=master;
by id dt;
run;
data sub_master;
input
ID dt : ddmmyy10. GRD;
cards;
111111 02/07/2013 1
111111 02/04/2014 1
100000 10/01/2017 5
100000 29/05/2018 2
111122 05/01/2018 1
;
proc sort data=sub_master;
by id dt;
run;
proc sql;
create table min_master as
select distinct a.id, max(a.dt), a.dt, a.grade as grd
from master as a,
sub_master as b
where a.grade = . and a.id = b.id and a.dt > b.dt
group by a.ID, b.id
having a.dt = min(a.dt) and b.dt = max(b.dt)
;
quit;
data want;
set sub_master
min_master(drop=_TEMG001);
format dt date10.;
run;
proc sort data=want;
by id dt;
run;
... View more
