Considering weekend when measuring days until event

[sasgorilla]

I have a data set with two dates. The first indicates when an event happened (date1). The second when an event resolved (date2). Resolution can only take place on a weekday due to business hours. 

I'm trying to figure out the best way to code in a data step to measure days until resolution (timetores) if:

1) I decide I want to not count the first weekend days only if date1 was on a weekend (i.e. weekday in (1 7)).

In other words, if date1 was on a Saturday or Sunday and date 2 was on a Monday I would want timetores to =0, as Monday was the first day with the opportunity to resolve. This timetores should be the same as if date1 and date 2 were on the same weekday. In either scenario, days after the first Monday or subsequent weekday would add +1 day. 

Weekend days following the first weekend, or any weekdays if Date1 occurred on a weekday, would add +1 day as with any other day. 

2) Alternatively, how would I code if I decide I want to not count any weekend days, regardless of when Date 1 took place. 

In this example I basically wouldn't penalize days where resolution could not take place. So if Date1 took place on Friday, the next Monday should start with 1. If date1 took place on a Monday, the following Monday timetores would =5 (Tue=1, Wed=2, Thu=3, Fri=4, Mon=5). 

Does this make sense? 

[quickbluefish]

I think you can do this - just set %let penalize_weekends to 0 or 1 (i.e., false / true) as needed (first bit is just some fake data):

data have;
do i=1 to 20;
	dt1='02Nov2024'd;
	dt2=dt1+i;
	output;
end;
drop i;
format dt1 dt2 date9.;
run;

%let penalize_weekends=0;

data want;
set have;
DT=dt1+(weekday(dt1)=7)*2+(weekday(dt1)=1);
timetores=0;
do while (DT<dt2);
    timetores+(weekday(DT)<6)+(weekday(DT)>5)*&penalize_weekends;
    DT+1;
end;
drop DT;
run;

proc print data=want; run;

[sasgorilla]

Thank you, quickbluefish. This produces the 2nd want (see the code I added in another reply to clarify between the two approaches I am looking for). It is good to see different ways to approach this problem. 

[Ksharp]

That would be better if you could post some example and desired output to illustrate what you are looking for.

data have;
input start :yymmdd10. end :yymmdd10.;
_start=start;
if weekday(start)=1 then _start=start+1;
if weekday(start)=7 then _start=start+2;
want=intck('weekday',_start,end);
drop _start;
format start end yymmdd10.;
cards;
2024-11-16 2024-11-18
2024-11-17 2024-11-18
2024-11-11 2024-11-18
2024-11-18 2024-11-18
2024-11-18 2024-11-19
;

[sasgorilla]

You are right, sorry for not including code for clarity. Please see below. 

data have;
input date1 :yymmdd10. date2 :yymmdd10.;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04
2024-11-04 2024-11-04
2024-11-05 2024-11-07
2024-11-07 2024-11-11
;
RUN;

data want1 /*don't count only 1st weekend if date1 on 1st weekend*/;
input date1 :yymmdd10. date2 :yymmdd10. timetores;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04 0
2024-11-04 2024-11-04 0
2024-11-05 2024-11-07 2
2024-11-07 2024-11-11 4
2024-11-09 2024-11-18 7
;
RUN;

data want2 /*never count weekend*/;
input date1 :yymmdd10. date2 :yymmdd10. timetores;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04 0
2024-11-04 2024-11-04 0
2024-11-05 2024-11-07 2
2024-11-07 2024-11-11 2
2024-11-09 2024-11-18 5
;
RUN;

[Reeza]

Your have data set has 4 records, but your want data sets have 5 records.

Are you sure for want2, the last one should be 5, not 6? If it is 6, I think this mostly works. 

data want;
set have;

timestores = intck('weekday', date1, date2);

*if start on weekend, move to first weekday;
if weekday(date1) in (1,7) then date_start = intnx('weekday', date1, 1, 'b');
else date_start = date1;

format date_start yymmdd10.;

*second calculation;
timestores2 = intck('day', date_start, date2);
run;

---

@sasgorilla wrote:

You are right, sorry for not including code for clarity. Please see below. 

data have;
input date1 :yymmdd10. date2 :yymmdd10.;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04
2024-11-04 2024-11-04
2024-11-05 2024-11-07
2024-11-07 2024-11-11
;
RUN;

data want1 /*don't count only 1st weekend if date1 on 1st weekend*/;
input date1 :yymmdd10. date2 :yymmdd10. timetores;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04 0
2024-11-04 2024-11-04 0
2024-11-05 2024-11-07 2
2024-11-07 2024-11-11 4
2024-11-09 2024-11-18 7
;
RUN;

data want2 /*never count weekend*/;
input date1 :yymmdd10. date2 :yymmdd10. timetores;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04 0
2024-11-04 2024-11-04 0
2024-11-05 2024-11-07 2
2024-11-07 2024-11-11 2
2024-11-09 2024-11-18 5
;
RUN;

---

[sasgorilla]

Sorry, that was a mistake to not include the 5th line of code in the HAVE dataset. 

Thanks for pointing that out!

[Patrick]

You could add some error handling for such cases. Something like:

if weekday(date2) in (1,7) then
  do;
  /* here some error handling code */
  end;

You could for example ensure that your time_stores variables become missing if date2 falls on a weekend and though is invalid. Or you could  "fix" the data and shift it on to the next Monday.

data want;
  set have;
  format date1 date2 shift_date1 shift_date2 weekdatx.;

  shift_date1=date1;
  shift_date2=date2;

  if weekday(shift_date2) in (1,7) then
  do;
    /* Option1: set shift_date2 to missing so derived variables become missing */
    /* shift_date2=.; */

    /* Option2: "fix" data by shifting date2 to Monday */
    shift_date2=intnx('weekday17w',shift_date2,1);
  end;


  /* if shift_date1 a Saturday or Sunday shift it to Monday */
  if weekday(shift_date1) in (1,7) then shift_date1=intnx('weekday17w',shift_date1,1);

  derived_timetores_1=shift_date2-shift_date1;
  /* derived_timetores_1=intck('day',shift_date1,shift_date2); */

  derived_timetores_2=intck('weekday17w',shift_date1,shift_date2);

run;

[Patrick]

It's always really helpful if sample data gets provided. Assuming I did create correct sample data below should work.

data have;
  input event_dt:date9. resolve_dt:date9. days2resolve_want;
  format event_dt resolve_dt date9.;
  datalines;
15nov2024 18nov2024 1
16nov2024 18nov2024 0
17nov2024 18nov2024 0
18nov2024 18nov2024 0
16nov2024 19nov2024 1
17nov2024 19nov2024 1
18nov2024 19nov2024 1
18nov2024 20nov2024 2
15nov2024 26nov2024 7
16nov2024 26nov2024 6
17nov2024 26nov2024 6
18nov2024 26nov2024 6
19nov2024 26nov2024 5
;

data want;
  set have;
  if weekday(event_dt) in (1,7) then days2resolve_derived=intck('weekday17w',intnx('weekday17w',event_dt,1),resolve_dt);
  else days2resolve_derived=intck('weekday17w',event_dt,resolve_dt);
run;

proc print data=want;
run;

[sasgorilla]

Thank you, Patrick. It looks like the code you provided works out the 2nd "want". I will read up on intck and intnx, as I don't think I've seen these before.  I've attached code below to clarify the difference between the 1st and 2nd want. 

data have;
input date1 :yymmdd10. date2 :yymmdd10.;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04
2024-11-04 2024-11-04
2024-11-05 2024-11-07
2024-11-07 2024-11-11
;
RUN;

data want1 /*don't count only 1st weekend if date1 on 1st weekend*/;
input date1 :yymmdd10. date2 :yymmdd10. timetores;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04 0
2024-11-04 2024-11-04 0
2024-11-05 2024-11-07 2
2024-11-07 2024-11-11 4
2024-11-09 2024-11-18 7
;
RUN;

data want2 /*never count weekend*/;
input date1 :yymmdd10. date2 :yymmdd10. timetores;
format date1 yymmdd10. date2 yymmdd10.;
datalines;
2024-11-02 2024-11-04 0
2024-11-04 2024-11-04 0
2024-11-05 2024-11-07 2
2024-11-07 2024-11-11 2
2024-11-09 2024-11-18 5
;
RUN;

[Patrick · Accepted solution]

---

@sasgorilla wrote:

Thank you, Patrick. It looks like the code you provided works out the 2nd "want". I will read up on intck and intnx, as I don't think I've seen these before.  I've attached code below to clarify the difference between the 1st and 2nd want. 

For working with dates understanding intnx() and intck() is crucial and will make your programming live much easier. 

Below amended code for calculation of both your wants.

/* timetores_1: don't count only 1st weekend if date1 on 1st weekend */
/* timetores_1: never count weekend                                  */
data have;
  input date1 :yymmdd10. date2 :yymmdd10. timetores_1 timetores_2;
  format date1 yymmdd10. date2 yymmdd10.;
  datalines;
2024-11-02 2024-11-04 0 0
2024-11-04 2024-11-04 0 0
2024-11-05 2024-11-07 2 2
2024-11-07 2024-11-11 4 2
2024-11-09 2024-11-18 7 5
;
run;

data want;
  set have;

  shift_date1=date1;

  /* if shift_date1 a Saturday or Sunday shift it to Monday */
  if weekday(shift_date1) in (1,7) then shift_date1=intnx('weekday17w',shift_date1,1);

  derived_timetores_1=date2-shift_date1;
  /* derived_timetores_1=intck('day',shift_date1,date2); */

  derived_timetores_2=intck('weekday17w',shift_date1,date2);

run;

proc print data=want;
run;

[sasgorilla]

Thanks, Patrick! This appears to have worked except there is one particular observation that is (-) for both values, (-2) for want1 and (-1) for want2. This is the only negative value out of a couple thousand observations. 

For this observation, Date1=04/09/2022 and Date 2=04/09/2022; 

Any ideas on how to troubleshoot to figure out what's causing this? 

[Patrick]

---

@sasgorilla wrote:

Thanks, Patrick! This appears to have worked except there is one particular observation that is (-) for both values, (-2) for want1 and (-1) for want2. This is the only negative value out of a couple thousand observations. 

For this observation, Date1=04/09/2022 and Date 2=04/09/2022; 

Any ideas on how to troubleshoot to figure out what's causing this? 

---

That's because your Date2 is on a Sunday which is obviously not a working day and though based on your initial definition shouldn't be possible. 

If you have to cater for such cases then you need to provide amended sample data that covers such cases, shows the desired result and explains the logic.

/* timetores_1: don't count only 1st weekend if date1 on 1st weekend */
/* timetores_1: never count weekend                                  */
data have;
  input date1 :yymmdd10. date2 :yymmdd10. timetores_1 timetores_2;
  format date1 yymmdd10. date2 yymmdd10.;
  datalines;
2024-11-02 2024-11-04 0 0
2024-11-04 2024-11-04 0 0
2024-11-05 2024-11-07 2 2
2024-11-07 2024-11-11 4 2
2024-11-09 2024-11-18 7 5
2022-09-04 2022-09-04 0 0
;
run;

data want;
  set have;
  format date1 date2 shift_date1 weekdatx.;

  shift_date1=date1;

  /* if shift_date1 a Saturday or Sunday shift it to Monday */
  if weekday(shift_date1) in (1,7) then shift_date1=intnx('weekday17w',shift_date1,1);

  derived_timetores_1=date2-shift_date1;
  /* derived_timetores_1=intck('day',shift_date1,date2); */

  derived_timetores_2=intck('weekday17w',shift_date1,date2);

run;

proc print data=want;
run;

[sasgorilla]

Ah, that makes sense. Sorry I didn't catch that, I was on the wrong calendar year so didn't see it was a Sunday.

I will assume it is an input error. Thanks for all the help!