Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- consecutive date variable duration

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-28-2023 10:14 AM
(504 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 2025** is scheduled for May 6-9 in Orlando, FL. Sign up to be **first to learn** about the agenda and registration!

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.

Ready to level-up your skills? Choose your own adventure.