Calculating the future dates

[karthigao]

 I have cycle 1 to 20, for first 3 cycle the lab is done on C1D1 C1D3 C1D8 C1D15 C1D22 C1D29 C1D36, after which Lab is performed only on  Day 1 (CxD1). now I need to calculate the future dates for the lab, when the dosing is present. how do I calculate the future dates for the lab based on the number of days from the Dosing date, only if the lab missing for all 20 cycles.

The dosing indicates the cycle 1 dosing,  cycle 2 dosing , so on....

This is the excel output(proc report) which has to be filled with the calculated dates 

Cycle 1

Cycle 2

LAB

Lab

Subject

EOT

Dosing

C1D1

C1D3

C1D8

C1D15

C1D22

C1D29

C1D36

Dosing

C2D1

C2D3

C2D8

C2D15

C2D22

C2D29

C2D36

1032

31Jan2023

29Nov2022

.

2005

10Jan2023

21Feb2023

2008

16Mar2023

19Jan2023

.

2009

26Apr2023

01Feb2023

.

2011

20Jan2023

08Mar2023

2013

17Aug2023

01Feb2023

15Mar2023

2014

03Feb2023

16Mar2023

2015

20Mar2023

06Feb2023

2016

03Feb2023

18Mar2023

2017

08May2023

13Feb2023

27Mar2023

[ballardw]

I can't follow your description because you don't have any variables named D1 D3 D8 D15 D22 D29 D36.

The "cycle" heading done that way is not valid in a SAS data set. What you show would not be a valid data set also because you imply having multiple variables named "dosing".

You should have a  variable on each observation that indicates the Cycle and not try to stick such information into additional multiple variables.

If you have an actual date valued SAS variable in a data set the function INTNX will allow you to create a value based on a date and increment it forward or backward. The first parameter is the interval to use, common are Day, Week, Month, Quarter, Year and a few others, the second is the base date value, the third is the number of intervals to add/subtract and last is an indicator S, same relative day of month or year for example, B , beginning of interval, or E, end of interval.

The interval is text

Newvar = intnx('day',dosing,1); would assign a value of 1 day greater than Dosing to the Newvar.

If you have multiple variables that you want to deal with than array may be the way to do such but it isn't clear what actual intervals might be involved. Another temporary array with the incrementing value might be the way to approach that.

Please provide an example of the data set that you currently have as data step code.

[karthigao]

Edited my questions.

[PaigeMiller]

Please explain, step-by-step, leaving nothing out, how we compute the value that goes in column C1D1 for subject 1032. What about column C1D3 for subject 1032? What about column C2D1 for subject 1032?

Since the data for subject 2005 is missing EOT, please explain the above questions for subject 2005.

[karthigao]

Dosing is the dose Date of Cycle 1(variable name is C1D1). if the dosing(C1D1) is not missing then I need to calculate the dates when the lab will be collected. the calculation is as below

C1D1 = date of C1D1

C1D3 = C1D1+3 days,

C1D5 = C1D1+5days,

C1D8= C1D1+8 days,

C1D15 = C1D1+15 days,

C1D22 = C1D1+22 days,

C1D29 = C1D1+29 days,

C1D36 = C1D1+36 days,

Anticipated Lab dates mentioned above have to be calculated for cycle 1 to cycle 4.

From cycle 5 to cycle 20 the lab dates are calculated only for C5D1, C6D1 , C7D1, etc....which will be the same date as dose date C5D1, C6D1 , C7D1, etc ... 

If there is no end of treatment  (EOT) then calculate the lab dates up to cycle 20. if the EOT date is present then cycle stop when cycle has reached the EOT date.  The Dosing dates exists for all subject until cycle 20 where the EOT is Not missing

[PaigeMiller]

To do this in SAS, we would need to see something like a SAS data set and not an Excel file. In a SAS data set, there cannot be two variables named DOSING.

In SAS, the words you wrote are the code you want, except with a semi-colon at the end instead of a comma.

---

C1D1 = date of C1D1

C1D3 = C1D1+3 days,

C1D5 = C1D1+5days,

C1D8= C1D1+8 days,

C1D15 = C1D1+15 days,

C1D22 = C1D1+22 days,

C1D29 = C1D1+29 days,

C1D36 = C1D1+36 days,

---

Except, you cannot define C1D1 as date of C1D1, this is circular logic and doesn't explain anything. I really still don't know what date C1D1 is.

However, to do this in SAS well, you don't want a table arranged as in Excel. You want a LONG data set, not a WIDE data set. Something like this:

subject     cycle_number        eot               dosing            num_days     date
1032         1                  31JAN2023      29NOV2022               1
1032         1                  31JAN2023      29NOV2022               3

[Tom]

 I have cycle 1 to 20, for first 3 cycle the lab is done on C1D1 C1D3 C1D8 C1D15 C1D22 C1D29 C1D36, after which Lab is performed only on  Day 1 (CxD1). now I need to calculate the future dates for the lab, when the dosing is present. how do I calculate the future dates for the lab based on the number of days from the Dosing date, only if the lab missing for all 20 cycles.

So if you have data like:

data have ;
  input subject cycle start :date. end :date.;
  format start end date9.;
cards;
1032 1 29NOV2022 31JAN2023
1032 2 19JAN2023 16MAR2023
2005 4 01FEB2021 15APR2021
;

You can generate a record for each expect lab sample date like this:

data want;
  set have;
  length labday $8 labdate 8;
  format labdate date9.;
  if cycle in (1:3) then do day=1,3,8 by 7 while((start+day-1)<=end);
    labday=cats('C',cycle,'D',day);
    labdate=start+day-1;
    output;
  end;
  else do;
    day=1;
    labday=cats('C',cycle,'D',day);
    labdate=start;
    output;
  end;
run;

Results:

Obs    subject    cycle        start          end    labday      labdate    day

  1      1032       1      29NOV2022    31JAN2023    C1D1      29NOV2022      1
  2      1032       1      29NOV2022    31JAN2023    C1D3      01DEC2022      3
  3      1032       1      29NOV2022    31JAN2023    C1D8      06DEC2022      8
  4      1032       1      29NOV2022    31JAN2023    C1D15     13DEC2022     15
  5      1032       1      29NOV2022    31JAN2023    C1D22     20DEC2022     22
  6      1032       1      29NOV2022    31JAN2023    C1D29     27DEC2022     29
  7      1032       1      29NOV2022    31JAN2023    C1D36     03JAN2023     36
  8      1032       1      29NOV2022    31JAN2023    C1D43     10JAN2023     43
  9      1032       1      29NOV2022    31JAN2023    C1D50     17JAN2023     50
 10      1032       1      29NOV2022    31JAN2023    C1D57     24JAN2023     57
 11      1032       1      29NOV2022    31JAN2023    C1D64     31JAN2023     64
 12      1032       2      19JAN2023    16MAR2023    C2D1      19JAN2023      1
 13      1032       2      19JAN2023    16MAR2023    C2D3      21JAN2023      3
 14      1032       2      19JAN2023    16MAR2023    C2D8      26JAN2023      8
 15      1032       2      19JAN2023    16MAR2023    C2D15     02FEB2023     15
 16      1032       2      19JAN2023    16MAR2023    C2D22     09FEB2023     22
 17      1032       2      19JAN2023    16MAR2023    C2D29     16FEB2023     29
 18      1032       2      19JAN2023    16MAR2023    C2D36     23FEB2023     36
 19      1032       2      19JAN2023    16MAR2023    C2D43     02MAR2023     43
 20      1032       2      19JAN2023    16MAR2023    C2D50     09MAR2023     50
 21      1032       2      19JAN2023    16MAR2023    C2D57     16MAR2023     57
 22      2005       4      01FEB2021    15APR2021    C4D1      01FEB2021      1