Flag data based on dates (first and last)

[NewUsrStat]

Hi guys, 

suppose to have the following dataset:

data DB;
  input ID :$20. (Date) (:mmddyy.) Line :$20. Treatment :$20.;
  format Date date9.;
cards;
0001 01/09/2024   1st line   Chemoth
0001 03/09/2024     .        Radio  
0001 04/09/2024     .        Radio 
0001 09/13/2024   1st line   Chemoth
0002 11/09/2016   1st line   Chemoth 
0003 06/30/2025   2nd line   Chemoth 
0003 07/31/2025   2nd line   Chemoth 
0004 03/12/2024      .       Radio 
;

 Is there a way to  flag the following: "if more than one 1st line of Chemotherapy was done, then take the Date of the last"? 

Similarly, is there a way to flag the following: "if more than one 2nd line of Chemotherapy was done, then take the Date of the first"?

Is there a way to put both rules in the same piece of code?

Thank you in advance

[Kathryn_SAS · Accepted solution]

You do not indicate what you want to do if Line is missing or if there is only one observation. However, here is something you can try.

data DB;
  input ID :$20. (Date) (:mmddyy.) Line $8. Treatment :$20.;
  format Date date9.;
cards;
0001 01/09/2024 1st line Chemoth
0001 03/09/2024   .      Radio  
0001 04/09/2024   .      Radio 
0001 09/13/2024 1st line Chemoth
0002 11/09/2016 1st line Chemoth 
0003 06/30/2025 2nd line Chemoth 
0003 07/31/2025 2nd line Chemoth 
0004 03/12/2024    .     Radio 
;
run;

proc sort data=db;
by id line treatment;
run;

data db1;
set db;
by id line treatment;
if last.treatment and line='1st line' then flag=1;
else if first.treatment and line='2nd line' then flag=1;
else flag=0;
run;

proc print;
run;

If you have additional questions, please show what you are expecting based on the data you sent.