Sending out alerts based on weekdays, need to accommodate for delay parameter

[TurnTheBacon]

I have a bit of a complex problem (at least from my point of view) that I'm hoping for some assistance with. It's regarding a DI Studio job that's used for sending out alerts to our consultants.

As you're all probably aware, weekday("&sysdate"d) returns a single numeric digit indicating the weekday. 1=Sun, 2=Mon, 3=Tue, etc. My job is scheduled to run on each business day, and the first thing it does is to determine the weekday.

If it's Monday (2), it sends alerts from Friday (6).

If it's Tuesday (3), it sends alerts from Monday (2).

If it's Wednesday (4), it sends alerts from Tuesday (3).

If it's Thursday (5), it sends alerts from Wednesday(4).

If it's Friday (6), it sends alerts from Thursday(5).

This has worked perfectly. However, now I need to implement an adjustable parameter (&days_delayed) for delaying the alerts. In example, if &days_delayed is set to 2, and the job is executed on a Thursday , it should send the alerts from Monday instead of Wednesday. The purpose of the delay is to give postal mail time to reach the customers before our consultants receive the alert and call them up. Because of this, the delay should avoid counting Saturdays and Sundays, because the postal mail doesn't make much progress on those days.

So far I've implemented the delay parameter so that if it's set to 2, and the job is executed on a Thursday , it sends the alerts from Monday instead of Wednesday. However, if &days_delayed was set to 3 instead, it would try to send the alerts from Sunday. In this example I would want it to send alerts from Friday, because that's the 4th business day back from Thursday.

If &days_delayed was a static value I could solve this by means of using precode like "if business_day=1 then business_day=6" etc., but I can't figure out how to do it when &days_delayed is supposed to be an easily adjustable value. I'm probably overlooking a simple solution, though.

Any guidance would be greatly appreciated.

[art297]

I'm not experienced with DI studio, thus can't suggest what you can and can't implement there.  However, that said, I think that the following would be applicable: the intervalds system option (see, e.g.: http://support.sas.com/resources/papers/proceedings12/040-2012.pdf )

You could easily build interval datasets, for each desired delay option, an assign the correct dataset based on the selected delay value.  The paper I mentioned, above, includes a number of examples of building such datasets, including building ones based on specific holidays.

[Astounding]

Here's an approach, although you might have to figure out how to use it within DI Studio.

days_delayed=0;

if &days_delayed > 0 then do until (days_delayed=&days_delayed);

   target_date = target_date - 1;

   if (1 < weekday(target_date) < 7) then days_delayed + 1;

end;

Good luck.

[Reeza]

can days_delayed be more than 1 week, ie cross the weekly divide so to speak?

[TurnTheBacon]

Thanks for the feedback so far everyone, I'm considering it now. Reeza, days_delayed will be in the range from 3-7, but no more. It's likely that it'll end up at 6 or 7. If set to 7, and the job was executed today (Tuesday 20th), it should send alerts from Thursday 8th rather than Monday 19th (skipping four weekend days).

[Reeza]

what about intnx with the weekday option? You may need to add or subtract 1 or play with the alignment option.

intnx('weekday', date, -1*&date_delayed);

[TurnTheBacon]

Thanks Reza, that function will likely prove very useful, though it doesn't get me past where I'm currently stuck I think. I need some logic that makes the job avoid situations like the one I mentioned in the 2nd last paragraph above ("So far I've implemented..."). Astounding's suggestion looks interesting, but I need to study it better to make good sense of it (perhaps he put days_delayed in a place where target_date ought to be, or perhaps I'm wrong?). I'll also study Arthur's resource tomorrow, it looks very interesting as well.

[Reeza · Accepted solution]

It should do exactly what you want, count back the number of delayed days counting only weekdays and returning only weekdays. Holidays aren't factored in though.

But ppl count 'days' differently so sometimes you need to add 1 to the number or subtract to get the date alignment you need.

For example you seem to be adding 1 day, Since two days before thursday would be a tuesday to me but you're saying monday.

Also in the example above, I'd consider Nov 9 to be 7 days before Nov 20, but you have Nov 8.

data _null_;

format today return date9.;

date_delayed=7;

today=date();

return=intnx('weekday', today, -1*date_delayed-1);

put 'date=' today;

put 'schedule=' return;

date_delayed=4;

today='22Nov2012'd;

return=intnx('weekday', today, -1*date_delayed);

put 'date=' today;

put 'schedule=' return;

run;

EDIT: if Thursday with 4 days should return Friday you don't need the -1, so I think in the other example you accounting for the holiday of November 11? Not sure. The second example demonstrates that. 

[art297]

: the "not including holidays" is why I had suggested creating intervalds datasets.  Your suggestion of using intnx with weekday would fit right into such a solution.

[TurnTheBacon]

Thank you very much everyone, the issue is resolved.

A little follow-up question: Can I make the INTJ-function automatically get me the date of the previous Friday no matter when the job is run, somehow?

[Reeza]

Use the alignment option and week as your interval.

I am going back one week and to the end of the week, so the Saturday. Then I delete one to get Friday.

But if you inputted a Saturday it goes back 8 or 9 days, if you don't care about weekends it will be fine.

data test;

format date prev_friday date9.;

    do date='01Nov2012'd to date();

        prev_friday=intnx('week', date, -1, 'end')-1;

        output;

    end;

run;

[TurnTheBacon]

Perfect, thanks again Reeza!