What do you want to do when there are two dates that are the same distance, but on different sides? Would you like to take the one that is BEFORE the 3 months post reference date or AFTER?
You need to decide how you want to count "months". Is it important to use actual calendar months? If so then the number of days will vary because the length of calendar months varies. It is usually only required to use calendar months when doing legal applications. This looks like a Medical application so it is better to just use a constant number of days so you don't need to worry about the varying length of calendar months. For a small number of months like 2 to 4 it is probably simplest to just use 30 day intervals as your proxy for "month".
First let's fix your example data step so it makes the DB dataset directly.
data DB;
input ID :$20. (Date ReferenceDate) (:mmddyy.) Outcome :$20.;
format Date ReferenceDate date9.;
cards;
0001 01/09/2024 01/06/2024 Response
0001 03/09/2024 01/06/2024 Response
0001 09/13/2024 01/06/2024 Response
0002 11/09/2016 06/30/2016 Complete Response
0002 09/20/2016 06/30/2016 Relapse
0003 06/30/2025 04/15/2025 Stable Disease
0003 07/31/2025 04/15/2025 Relapse
0004 03/12/2024 12/13/2023 Complete Response
;
So you can create FLAG1 directly reading the data as it is. But to make FLAG2 you will first need to find the difference between DATE and the 3 month target date so you can discover which one is closest.
You could do the calculation and ordering in one step by using PROC SQL.
proc sql;
create table step1 as
select *
, (date between referencedate+60 and referencedate+120) as Flag1
, case when calculated Flag1 then abs(referencedate+90-date)
else .
end as diff3month
from db
order by id, diff3month desc
;
quit;
Then you can use another data step to flag the closest date.
data want;
set step1;
by id ;
Flag2 = first.id and Flag1;
run;
Results
... View more
