consecutive date variable duration

[SAS_Learner2]

Hello,

I have n number of variables. I want to calculate consecutive date variable difference and if duration is 50 days then I want flag that particular record.

Below 001 subject has 5 dates ..

If date3-date1 or date4-date2 or date5-date3 is 50 days then flag as "Y".

002 has only one date1 so no need to flag.

if we have 10 dates for any subject we should calculate date3-date1, date4-date2, date5-date3, date6-date4 so on.

id    date1 date2 date3 date4 date5

001 13JUL2021 24FEB2022 28MAY2021 07JUN2021 28JUN2021

002 15NOV2021 . . . . .

[Tom]

Dates are measured in days.  How are you going to test for a duration in hours?

[SAS_Learner2]

Sorry, My bad. It should be days only not hours.

[ballardw · Accepted solution]

Your shown interval calculation shows that 3 date values are needed: date3-date1 . So your statement "002 has only one date1 so no need to flag." doesn't cover the case of exactly 2 dates present. Should there be any flag when you only have 2 values?

You say >50 but some of your dates are going to generate negative numbers larger than 50. Are those to be treated as 50?

Consider date4 and date2 for Id 001. That is 07JUN2021-24FEB2022 =  -262 days.

What should the flag look like?

Here is a first stab, including providing example data in the form of data step code so we can test with values. This assumes that 1) 3 dates are needed, 2) that you want the absolute value to set the flag so -262 is treated as 262 days and 3) only one flag is wanted and is numeric 1 when set.

data have;
   informat id $5.    date1 date2 date3 date4 date5 date9.;
   input id $    date1 date2 date3 date4 date5;
   format date: date9.;
datalines;
001 13JUL2021 24FEB2022 28MAY2021 07JUN2021 28JUN2021
002 15NOV2021 . . . . .
;

data want;
   set have;
   array d (*) date: ;
   numdates= n(of d(*));
   if numdates>3 then do i=1 to (numdates-2);
     /*abs =>absolute value of the subtraction*/
     if abs(d[i+2]-d[i]) > 50 then do;
        Flag=1;
        leave;
     end;
  end;
  drop numdates i;
run;

Warning: this assumes that the date variables do not have gaps, i.e. missing values in the middle of the list, and start at date1. If the data is other than that description you need to provide more complete example data and rule for handling gaps.

[SAS_Learner2]

Thank you for your prompt response.

Yes, we have to take absolute days with they are going to negative.

There should not be any flag if only only date1 or/and date2 are present.

Many Thanks.