Calculate the future date for reminder of the visit

[karthigao]

My data looks like below,

SubjectID	instancename	Dosing date	Dosing interval (weeks)
0001	C1D1	1-Jan-22
0001	C2D1	22-Jan-22	3
0001	C3D1	12-Feb-22	3
0001	C4D1	26-Mar-22	6
0001	C5D1	7-May-22	6
0001	C6D1	19-Jun-22	6
0001	C7D1	31-Jul-22	6
0001	C8D1	11-Sep-22	6
0001	C9D1	23-Oct-22	6
0001	C10D1	4-Dec-22	6
0002	C1D1	14-Feb-23
0002	C2D1	7-Mar-23	3
0002	C3D1	28-Mar-23	3
0003	C1D1	10-Jan-22
0003	C2D1	2-Feb-22	3
0003	C3D1	22-Feb-22	3
0003	C4D1	6-Apr-22	6
0003	C5D1	17-May-22	6
0003	C6D1	29-Jun-22	6
0004	C1D1	29-Mar-23
0005	C1D1	14-Apr-23

and so no....

for each subjectID the visit are upto 15 cycles and I would like to calculate the future dates based on the Dosing interval, example: I need calculate the dates for C4D1 and beyond for subject and for 0004 C2D1 and beyond.  also if there is end of treatment date in between then I wanted to stop the calculation for future visits, please help me on how to code this. If possible I would like to use the previous cycle dates for calculation rather than the C1D1 if prior cycle is not missing. 

[Reeza]

It's great that you've provided sample data!

Can you also provide some expected output? If this was the raw data what would you expect as the output (exactly?

[karthigao]

Hi Below is what I am looking for. the Red color fonts are to be calculated for all the expected visits. (this is one part of a tracker I am programming) 

SubjectID	instancename	Dosing date	Dosing interval (weeks)	Calculated Date
0001	C1D1	1-Jan-22
0001	C2D1	22-Jan-22	3
0001	C3D1	12-Feb-22	3
0001	C4D1	26-Mar-22	6
0001	C5D1	7-May-22	6
0001	C6D1	19-Jun-22	6
0001	C7D1	31-Jul-22	6
0001	C8D1	11-Sep-22	6
0001	C9D1	23-Oct-22	6
0001	C10D1	4-Dec-22	6
0001	C11D1		6	15-Jan-23
0001	C12D1		6	26-Feb-23
0001	C13D1		6	9-Apr-23
0002	C1D1	14-Feb-23
0002	C2D1	7-Mar-23	3
0002	C3D1	28-Mar-23	3
0002	C4D1			9-May-23
0002	C5D1			20-Jun-23
0002	C6D1			1-Aug-23
	….and other future visits
0003	C1D1	10-Jan-22
0003	C2D1	2-Feb-22	3
0003	C3D1	22-Feb-22	3
0003	C4D1	6-Apr-22	6
0003	C5D1	17-May-22	6
0003	C6D1	29-Jun-22	6
0003	C7D1			3-Aug-22
	….and other future visits
0004	C1D1	29-Mar-23
0004	C2d1
0005	C1D1	14-Apr-23
0005	C2D1

[Reeza · Accepted solution]

data have;
    infile cards truncover;
    informat subjectID $4. instancename $8. dosingDate date11. dosingInterval 8.;
    format subjectID $4. instancename $8. dosingDate date11. dosingInterval 8.;
    input SubjectID instancename Dosingdate Dosinginterval;
    cards;
0001    C1D1    1-Jan-22     
0001    C2D1    22-Jan-22   3
0001    C3D1    12-Feb-22   3
0001    C4D1    26-Mar-22   6
0001    C5D1    7-May-22    6
0001    C6D1    19-Jun-22   6
0001    C7D1    31-Jul-22   6
0001    C8D1    11-Sep-22   6
0001    C9D1    23-Oct-22   6
0001    C10D1   4-Dec-22    6
0002    C1D1    14-Feb-23    
0002    C2D1    7-Mar-23    3
0002    C3D1    28-Mar-23   3
0003    C1D1    10-Jan-22    
0003    C2D1    2-Feb-22    3
0003    C3D1    22-Feb-22   3
0003    C4D1    6-Apr-22    6
0003    C5D1    17-May-22   6
0003    C6D1    29-Jun-22   6
0004    C1D1    29-Mar-23    
0005    C1D1    14-Apr-23    
;
    ;
    ;;
run;

data want;
    set have;
    by subjectid;

    if first.subjectid then
        counter=0;
    counter+1;

    if last.subjectID and counter le 15 then
        do;
            output;
            futuredosingDate=dosingDate;
            dosingDate=.;

            do i=counter+1 to 15;
                instancename=catt('C', put(i, 8. -l), 'D1');
                futureDosingDate=intnx('week', futuredosingDate, dosingInterval, 's');
                output;
            end;
        end;
    else
        output;
    format futuredosingDate date11.;
run;