topic Re: Dates between two dates based on condition in SAS Programming

Dates between two dates based on condition

jmmedina252 — Fri, 05 May 2023 16:56:41 GMT

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

Re: Dates between two dates based on condition

Reeza — Fri, 05 May 2023 17:19:25 GMT

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

Re: Dates between two dates based on condition

jmmedina252 — Fri, 05 May 2023 18:17:21 GMT

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

Re: Dates between two dates based on condition

Reeza — Fri, 05 May 2023 19:27:45 GMT

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;

Re: Dates between two dates based on condition

jmmedina252 — Wed, 17 May 2023 20:14:52 GMT

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?

Re: Dates between two dates based on condition

Reeza — Wed, 17 May 2023 20:19:09 GMT

Please show an example.

Re: Dates between two dates based on condition

jmmedina252 — Wed, 17 May 2023 20:39:49 GMT

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

Re: Dates between two dates based on condition

Reeza — Wed, 17 May 2023 20:59:14 GMT

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;