Dates between two dates based on condition

[jmmedina252]

Hi, I'm looking to get the dates in between 2 dates according to the criteria of a third variable.  Here is what my data looks like:

Animal       Visit          Date

100            Day -1      05/19/21

100            Day 14     06/03/21

100            Day 28     06/17/21

100            Day 42     07/01/21

100            Day 70     07/28/21

100            Day 84     08/11/21

I need the dates in between when visit= 'Day 28' and visit= 'Day 42' and then the dates between when visit= 'Day 70' and visit= 'Day 84', with an addition variable to signify the days calculated as 'observation' and the visit days as 'visit'

Also, I need 14 consecutive days calculated after visit= 'Day 84'.  I presume an intck or intx formula would need to be used to avoid going past the correct last day of the month in a given calendar year.

The output would look like this:

Animal       Visit          Date           Label  

100            Day -1      05/19/21     visit

100            Day 14     06/03/21     visit

100            Day 28     06/17/21     visit

100                            06/18/21      observation

100                            06/19/21      observation

100                            06/20/21      observation

...

100            Day 42     07/01/21     visit

100            Day 70     07/28/21     visit

100                            07/29/21      observation

100                            07/30/21      observation

100                            07/31/21      observation

...

100            Day 84     08/11/21     visit

100                             08/12/21     observation

100                             08/13/21     observation

100                             08/14/21     observation

...

I put '...' to signify that the iterated rows would continue until it reaches the end point designated by visit

[Reeza]

Are those intervals hardcoded? They are the same regardless of the animal?

[jmmedina252]

Yes, intervals are hardcoded and they are the same regardless of animal

[Reeza]

data have;
    infile cards dsd;
    input Animal  $      Visit $   Date : mmddyy10.;
    output;
    animal='200';
    output;
    format date yymmdd10.;
    cards;
100,Day -1,      05/19/21
100,Day 14,     06/03/21
100,Day 28,     06/17/21
100,Day 42,     07/01/21
100,Day 70,     07/28/21
100,Day 84,     08/11/21
;
run;

proc sort data=have;
    by animal date;
run;

data add_dates;
    set have;
    where visit in ('Day 28', 'Day 70', 'Day 84');
    label='observation';
    call missing(visit);

    if visit in('Day 28', 'Day 70') then
        do date=date+1 to date+12;
            output;
        end;
    else
        do date=date+1 to date+14;
            output;
        end;
run;

data want;
set have (in=t1) add_dates;
if t1 then label='Visit';
run;

proc sort data=want;
by animal date;
run;

[jmmedina252]

Hi Reeza, I was wondering if you could help with an issue I'm now having with this data. Basically, it is not 14 days after but after day 24 but instead the number of days after day 24 until day 42. The same with the other dates so populating dates between day 70 and day 84. Is there a way to do this- create dates in between those visits?

[Reeza]

Please show an example.

[jmmedina252]

Day 28 needs observation every day until day 42, day 70 needs observation days every day until day 84 and after the last dose there needs to be 28 days of observation.  What was happening before is that 14 days after day 28 may over flow onto the day 42 visit and even a few days after because day 28 and day 42 are not always 14 days apart.  I don't want observation on visit days or when during periods where observation is not required.

Animal       Visit          Date          Label

100            Day 28     06/17/21    Visit

100                            06/18/21   Observation

...

100                            06/30/21   Observation

100            Day 42     07/01/21

100            Day 70     07/28/21

100                            07/29/21   Observation

....

100                             08/10/21  Observation

100          Last Dose  08/10/21   Visit

100            Day 84     08/11/21   Visit

100                             08/12/21   Observation

....

since the last dose's date is a day before the visit, observation is not required on 8/11/21 but is expected 28 days after the last dose other than that day

[Reeza]

data have;
    infile cards dsd;
    input Animal  $      Visit $   Date : mmddyy10.;
    output;
    animal='200';
    output;
    format date yymmdd10.;
    cards;
100,Day -1,      05/19/21
100,Day 14,     06/03/21
100,Day 28,     06/17/21
100,Day 42,     07/01/21
100,Day 70,     07/28/21
100,Day 84,     08/11/21
;
run;

proc sort data=have;
    by animal date;
run;

data temp;
    merge have have(firstobs=2 rename=Date=nextDate keep=animal date);
    by animal;
run;

data expand;
    set temp;
    by animal;
    length label $20.;
    label='visit';
    output;

    if visit in('Day 28', 'Day 70') then
        do date=date+1 to nextdate;
            call missing(visit);
            label='observation';
            output;
        end;

    if last.animal then
        do date=date+1 to date+28;
            call missing(visit);
            labell='observation';
            output;
        end;
run;