Assign variable values to time intervals

[NewUsrStat]

Dear all, 

suppose to have the following dataset: 

data DB;
  input ID :$20. (Ref_date) (:mmddyy.) (Date_event) (:mmddyy.) Event :$20.;
  format Ref_date date9. Date_event date9.;
cards;
0001 09/01/2024  10/11/2024  Y
0001 09/01/2024  11/11/2024  Y
0001 09/01/2024       .      N
0001 09/01/2024  01/15/2025  Y
0002 09/11/2016  04/15/2016  Y
0003 30/06/2025  02/09/2025  Y
0003 30/06/2025  12/12/2025  Y
0004 12/03/2024       .      N
;

Is there a way to get the following? 

data DB1;
  input ID :$20. (Ref_date) (:mmddyy.) (Date_event) (:mmddyy.) Event :$20. Flag   :$20.;
  format Ref_date date9. Date_event date9.;
cards;
0001 09/01/2024  10/11/2024  Y  3M
0001 09/01/2024  11/11/2024  Y   .
0001 09/01/2024       .      N   .
0001 09/01/2024  01/15/2025  Y  6M
0002 09/11/2016  04/15/2016  Y  9M
0003 30/06/2025  02/09/2025  Y   .
0003 30/06/2025  12/12/2025  Y  6M 
0004 12/03/2024       .      N   .
;

The desired output is based on the following rules: 

1) if the Event = "Y" and date_event  > Ref_date then add a new column (variable) called "Flag" with values = 3M, 6M, 9M, 12M if the event occurs during the 3,6,9,12 months from the Ref_date. 

Consider that time intervals are intended as follows: 

from the Ref_date to 3M = 3M from the Ref_date

between 3M and 6M =6M from the Ref_date

between 6M and 9M =9M from the Ref_date

between 9M and 12M =12M from the Ref_date

Can anyone help me please? 

[andreas_lds]

Expected results, data and description don't match.

In the first observation, from 9Jan2024 to 11Oct2024 is nine month, why do you expect 3M?

[NewUsrStat]

Sorry, ddmm were inverted in Ref_date. I edited accordingly.

[PaigeMiller]

A couple of questions

1. In the first record of data set DB, it seems to me that 9 months have elapsed but you show this should have a value of 3M in variable FLAG. I don't understand this.
2. You don't make clear if there is a different way to handle things when REF_DATE is before DATE_EVENT or when REF_DATE is after DATE_EVENT.
3. In your rules describing separation of months into 3M, 6M, 9M, etc., a literal reading of these rules seems like 3 months would get a FLAG value of BOTH 3M and 6M.

Ignoring these issues, and guessing at the answers about how to handle things, this is easily handled by the INTCK function and a custom format. Naturally if I have guessed wrong about how you want to handle things, you definitely ought to try fixing this code by yourself.

proc format;
    value monthf 0-3='3M' 4-6='6M' 7-9='9M' 10-12='12M' other='.';
run;
data db1;
    set db;
    delta_months=intck('month',ref_date,date_event);
    format delta_months monthf.;
run;

Why use custom formats here? Because if you use custom formats, the 3M 6M 9M 12M sort properly. If you don't use custom formats, 12M sorts before 3M.

Also, thank you for providing rules rather than (or in addition to) examples, and thank you for providing example data in the desired format.

[NewUsrStat]

Sorry I edited. ddmm were inverted in Ref_date. I edited accordingly.

[PaigeMiller]

I asked for clarification on 3 issues, you only explained the first one.

If you are using informat mmddyy. then row 7 and 8 don't make sense, it shows month number equal to 30. (PS: advice is always to test your code first, make sure it works and does what you want it to do before providing it to us; you will get better and faster answers if you provide us with debugged code)

[yabwon]

Something like this:

data DB;
  input ID :$20. (Ref_date) (:mmddyy.) (Date_event) (:mmddyy.) Event :$20.;
  format Ref_date date9. Date_event date9.;
cards;
0001 09/01/2024  10/11/2024  Y
0001 09/01/2024  11/11/2024  Y
0001 09/01/2024       .      N
0001 09/01/2024  01/15/2025  Y
0002 09/11/2016  04/15/2016  Y
0003 30/06/2025  02/09/2025  Y
0003 30/06/2025  12/12/2025  Y
0004 12/03/2024       .      N
0005 12/03/2024  12/03/2024  Y
0005 12/03/2024  12/04/2024  Y
0006 12/03/2023  12/04/2024  Y
;
data db1;
    set db;
    length delta_months $ 3;
    if Event="Y" then delta_months=cats(3*(1<>abs(intck('month3',ref_date,date_event))),"M");
run;

proc print data=db1;
run;

?

Bart

[Tom]

So assuming that by REF_DATE you mean the variable INDEX_DATE.

Let's also correct your example to more closely match your problem description (and represent the dates in YMD order to avoid confusing the number of the month with the day of the month).

data have;
  input ID :$20. (Index_date Date_event) (:yymmdd.) Event :$20. Flag :$20.;
  format Index_date Date_event yymmdd10.;
cards;
0001 2024-09-01 2024-10-11 Y 3M
0001 2024-09-01 2024-11-11 Y 3M
0001 2024-09-01 .          N . 
0001 2024-09-01 2025-01-15 Y 6M
0002 2016-09-11 2017-04-15 Y 9M
0003 2025-06-30 2025-02-09 Y . 
0003 2025-06-30 2025-12-12 Y 6M
0004 2024-12-03 .          N .
;

So we can use the INTCK() function to check the number of month boundaries crossed. I have chosen to use the continuous method instead of testing for when it crosses day one of the month.   But you also might want to use a fixed number of days instead to avoid the fact that calendar months are not all the same length.

To convert from number of months to your 3,6,9,... values you can use the CEIL() function.

data want;
  set have;
  after = (date_event > index_date);
  if after then diff = intck('month',index_date,date_event,'c');
  diff3 = 3*ceil(diff/3);
run;

 Results

[dxiao2017]

Hi @NewUsrStat , I think firstly there is something wrong with your raw data , probably it is the informat,  column width, maybe also the column type, which are not properly set for the date columns and maybe also the ID column. I copy and paste your code for the dateset, when print it, it does not display properly, regardless it is ddmmyy. or mmddyy. informat, the output of both were not correctly displayed (has missing values that was not supposed to be missing). The code, log, and output are as follows, just for your reference. So I suggest that the first thing is to modify the raw dataset to create a good one with right informat, format, width, and column type.

The other suggestion is that, when create that new column (I think it is something like event time to be further used in, like, survival analysis), it is good practice to create two columns, one is number, the other is unit, for example, 3 months, the number column value is 3 and the unit column is month. So that when perform survival analysis you do not need to separate the character column (like what you create now) and convert it to number again, consumes a lot of time and labor.

data DB;
  input ID :$20. (Ref_date) (:mmddyy.) (Date_event) (:mmddyy.) Event :$20.;
  format Ref_date date9. Date_event date9.;
cards;
0001 09/01/2024  10/11/2024  Y
0001 09/01/2024  11/11/2024  Y
0001 09/01/2024       .      N
0001 09/01/2024  01/15/2025  Y
0002 09/11/2016  04/15/2016  Y
0003 30/06/2025  02/09/2025  Y
0003 30/06/2025  12/12/2025  Y
0004 12/03/2024       .      N
;
run;
proc print data=db;run;
data DB;
  input ID :$20. (Ref_date) (:ddmmyy.) (Date_event) (:ddmmyy.) Event :$20.;
  format Ref_date date9. Date_event date9.;
cards;
0001 09/01/2024  10/11/2024  Y
0001 09/01/2024  11/11/2024  Y
0001 09/01/2024       .      N
0001 09/01/2024  01/15/2025  Y
0002 09/11/2016  04/15/2016  Y
0003 30/06/2025  02/09/2025  Y
0003 30/06/2025  12/12/2025  Y
0004 12/03/2024       .      N
;
run;
proc print data=db;run;

69         data DB;
 70           input ID :$20. (Ref_date) (:mmddyy.) (Date_event) (:mmddyy.) Event :$20.;
 71           format Ref_date date9. Date_event date9.;
 72         cards;
 
 NOTE: Invalid data for Ref_date in line 78 6-15.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 78         0003 30/06/2025  02/09/2025  Y
 ID=0003 Ref_date=. Date_event=09FEB2025 Event=Y _ERROR_=1 _N_=6
 NOTE: Invalid data for Ref_date in line 79 6-15.
 79         0003 30/06/2025  12/12/2025  Y
 ID=0003 Ref_date=. Date_event=12DEC2025 Event=Y _ERROR_=1 _N_=7

84         data DB;
 85           input ID :$20. (Ref_date) (:ddmmyy.) (Date_event) (:ddmmyy.) Event :$20.;
 86           format Ref_date date9. Date_event date9.;
 87         cards;
 
 NOTE: Invalid data for Date_event in line 91 18-27.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 91         0001 09/01/2024  01/15/2025  Y
 ID=0001 Ref_date=09JAN2024 Date_event=. Event=Y _ERROR_=1 _N_=4
 NOTE: Invalid data for Date_event in line 92 18-27.
 92         0002 09/11/2016  04/15/2016  Y
 ID=0002 Ref_date=09NOV2016 Date_event=. Event=Y _ERROR_=1 _N_=5