BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
karthigao
Fluorite | Level 6

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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;

View solution in original post

3 REPLIES 3
Reeza
Super User
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
Fluorite | Level 6

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
Super User
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;

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 273 views
  • 1 like
  • 2 in conversation