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;
... View more
