SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-20-2012 05:32 PM
Super User
Posts: 19,855

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

Posted in reply to TurnTheBacon

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. 

View solution in original post


All Replies
PROC Star
Posts: 7,487

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

Posted in reply to TurnTheBacon

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.

Super User
Posts: 5,516

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

Posted in reply to TurnTheBacon

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.

Super User
Posts: 19,855

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

Posted in reply to TurnTheBacon

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

Frequent Contributor
Posts: 89

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

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

Super User
Posts: 19,855

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

Posted in reply to TurnTheBacon

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);

Frequent Contributor
Posts: 89

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

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.

Solution
‎11-20-2012 05:32 PM
Super User
Posts: 19,855

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

Posted in reply to TurnTheBacon

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. 

PROC Star
Posts: 7,487

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

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

Frequent Contributor
Posts: 89

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

Thank you very much everyone, the issue is resolved. Smiley Happy

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?

Super User
Posts: 19,855

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

Posted in reply to TurnTheBacon

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;

Frequent Contributor
Posts: 89

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

Perfect, thanks again Reeza!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 389 views
  • 7 likes
  • 4 in conversation