Below code gives you the output as required -
*sort data by patient id and date;
proc sort data= have; by pt_id date; run;
*create temporary table to format date_code & assign first and last biop indicators;
data _temp_want1_;
set have;
by pt_id date;
format date date9.;
if Month_diag <= 9 then
do;
_temp_Date_code = catx('/',('0'||strip(substr((put(Month_diag,$2.)),1,2))),'01',year_diag);
end;
else if Month_diag > 9 then
do;
_temp_Date_code = catx('/',(strip(substr((put(Month_diag,$2.)),1,2))),'01',year_diag);
end;
_temp_Date_code2 = input(_temp_Date_code, MMDDYY10.);
Date_code = _temp_Date_code2;
format Date_code date9.;
drop _temp_Date_code _temp_Date_code2;
If first.pt_id then
do;
first_biop = biop;
end;
If last.pt_id then
do;
last_biop = biop;
end;
run;
*inner table: calculate latest date pre diagnosis date to use in dt_biop assignment;
*outer table t2: join inner table to first temporary table to assign temporary dt_biop;
proc sql;
create table _temp_want_biop_dte_join as
select t1.*
, t2. temp_dt_biop
from _temp_want1_ t1
left join (select pt_id
, date
, case
when max(date) = date and date < date_code then date
else . end as temp_dt_biop format=date9.
from _temp_want1_
group by pt_id
having biop = 1 and max(date) = date) t2
on t1.pt_id = t2.pt_id
and t1.date = t2.date;
quit;
*assign temporary dt_biopsy across all rows and rename to the final dt_biopsy field;
proc sql;
create table _temp_want_biop_dte_join_max as
select *
, max(temp_dt_biop) as dt_biop format=date9.
from _temp_want_biop_dte_join
group by pt_id;
quit;
*inner table t1: select all data where mri = 1
*inner table t1: from table t1, group by patient id to select the latest date where mri = 1 and date is less than biopsy date;
*outer table: select only patient id, date and temporary mri date to join on to full table on the next steps;
proc sql;
create table _temp_want_mri_dte as
select t2.pt_id
, t2.date
, t2.temp_dt_mri
from (select t1.*
, case
when max(t1.date) = date and date < dt_biop then date
else . end as temp_dt_mri format=date9.
from (select *
from _temp_want_biop_dte_join_max
where mri = 1) t1
group by t1.pt_id) t2
where t2.temp_dt_mri = t2.date;
quit;
*join temprary mri date onto full data for the same date as where mri = 1 & date is less than biopsy date;
proc sql;
create table _temp_want_mri_dte_join as
select t1.*
, t2.temp_dt_mri
from _temp_want_biop_dte_join_max t1
left join _temp_want_mri_dte t2
on t1.pt_id = t2.pt_id
and t1.date = t2.date;
quit;
*fill in temporary mri date across all rows in full data;
proc sql;
create table _Temp_want_max_mri_dte as
select *
, max(temp_dt_mri) as dt_mri format=date9.
from _temp_want_mri_dte_join
group by pt_id;
quit;
*select single row per patient id with required fields & formats;
proc sql;
create table want as
select distinct pt_id
, put(date_code, monname3.) as month_diag
, year(date_code) as year_diag
, dt_biop format=MMDDYY10.
, dt_mri format=MMDDYY10.
from _Temp_want_max_mri_dte;
quit;
/*delete temporary tables*/
proc sql;
drop table _temp_want1_;
drop table _temp_want_biop_dte_join;
drop table _temp_want_biop_dte_join_max;
drop table _temp_want_mri_dte;
drop table _temp_want_mri_dte_join;
drop table _Temp_want_max_mri_dte;
quit;
