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

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 . . . . .

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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

SAS_Learner2
Calcite | Level 5

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

 

ballardw
Super User

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
Calcite | Level 5

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.

 

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 137 views
  • 0 likes
  • 3 in conversation