topic Omitting Holidays weekend from Date function in SAS Procedures

Omitting Holidays weekend from Date function

deleted_user — Tue, 01 Feb 2011 13:52:24 GMT

I have a function that changes the the date from our mainframe to Julian date, and then we run a query to check 2 business days back,

This is pretty tedious

The function is as follows

OPNDATE = DATEJUL(ODATE);
IF (ODATE<2011028 OR ODATE>2011028) THEN DELETE;

So every day we have to update the julian date to be 2 business days behind.

Would there be an optimal way to perform this function rather then haveing to update it daily.

Is there a way simply to calculate 2 business days behind which would include omitting weekend and holidays, If omitting holidays means creating a control card, i would be ok with this, but wouldnt know how to proceed after that.

Hope this makes sense

Thanks for your time and looking forward to some great Ideas 🙂

Re: Omitting Holidays weekend from Date function

advoss — Tue, 01 Feb 2011 17:59:49 GMT

This is ugly, but it works. I assumed that you are just working with 2011, but it is expandable. I created an informat HOLIDAY that returns 1 if the date is a holiday or 0 if not.
proc format ;
invalue holiday '01JAN2011'd,'25JAN2011'd,
'23DEC2011'd,'25DEC2011'd,
'26DEC2011'd = 1
other=0;
run;

data dates2011; /* generate test dates for 2011 */
do date = '01JAN2011'd to '31DEC2011'd;
output;
format date date9.;
end;
run;
data dates2011a;
set dates2011;
busday_2before = date-2;
xloop = 0;
LOOP:;
xweekday = weekday(busday_2before);
xloop = xloop+1;
if xloop > 5 then stop;
if xweekday in (1,7) then do;
if xweekday = 1 then busday_2before = busday_2before-2;
else busday_2before = busday_2before-1;
end;
if input(put(busday_2before,5.),holiday.) then do;
busday_2before = busday_2before-1;
goto loop; /* make sure not Saturday, sunday, or holiday, again */
end;
format busday_2before date9.;
run;

Re: Omitting Holidays weekend from Date function

Peter_C — Tue, 01 Feb 2011 18:18:22 GMT

Before SAS9.2 you can determine the date 2 working days before the rundate with the INTNX() function as displayed by

209 %put wd_minus2 = %sysfunc( intnx( weekday, "&sysdate"d, -2), weekdate );

wd_minus2 = Friday, January 28, 2011

Of more use would be the date as a sas date constant, like

%let wd_minus2 = %sysfunc( intnx( weekday, "&sysdate"d, -2), date9 );

Then it can be used like

if opnDate LT "&wd_minus2"d or opnDate GT "&wd_minus2"d

if you would like it as a julian date formatted string then adapt the above

%let wd_minus2J = %sysfunc( intnx( weekday, "&sysdate"d, -2), julian7 );

but that won't work as a date constant.

However, interval WEEKDAY does not provide logic to step over holiday dates

if you have SAS9.2 you can enjoy the new system option INTERVALDS.

This enbles you to define and use custom intervals which could combine weekends and your dates from a table

like:

libname my (work);

data my.hols;

input date :date9. ;

format date weekdate. ;

list;cards ;

27Jan11

26jan11

24jan11

1feb2011

;

* limit period of interest to last 20 weekdays;

%let tod = &sysdate ;

%let frd = %sysfunc( intnx( weekday, "&tod"d, -20 ), date9 ) ;

data span ;

format begin end weekdate. ;

keep begin end ;

* build the diary - holidays first ;

array dates(%sysevalf("&frd"d):%sysevalf("&tod"d)) $1 temporary ;

do while( not eof1 ) ;

set my.hols( where=( date between "&frd"d and "&tod"d )) end=eof1 ;

dates(date) ='H' ;

end ;

* now mark weekends ;

s1 = "&frd"d + 7 - weekday( "&frd"d ) ;* the first Saturday ;

do date= s1 to "&tod"d by 7 ; * saturday to saturday ;

dates( date) = 'W' ;

if "&tod"d GT date then dates( date+1) ='W' ; * Sunday ;

end ;

* now build custom intervals ;

do date= "&frd"d to "&tod"d ;

if dates(date) = ' ' then do;

if oldDay ne . then do;

begin = oldDay ;

end = date-1 ;

output ;

oldDay = date ;

end ;

else oldDay = coalesce( oldDay, date-1 ) ;

end ;

* check if diary finished on non-working day ;

if oldDay ne . then do;

begin = oldDay ;

end = "&tod"d ;

output ;

end ;

stop ;

format begin end weekdate. ;

run ;

option intervalDS = ( myWorkingDay = span );

%put last_wd_ex_hols = %sysfunc( intnx( myWorkingDay, "&sysdate"d, -2 ), weekdate) ;

%put last_wd_inc_all = %sysfunc( intnx( weekday , "&sysdate"d, -2 ), weekdate) ;

here is the log of the final results

207 %put last_wd_ex_hols = %sysfunc( intnx( myWorkingDay, "&sysdate"d, -2 ), weekdate);

last_wd_ex_hols = Tuesday, January 25, 2011

208 %put last_wd_inc_all = %sysfunc( intnx( weekday , "&sysdate"d, -2 ), weekdate);

last_wd_inc_all = Friday, January 28, 2011

demonstrating the effect of marking today, 26th January and 27th as holidays.

peterC

Message was edited by: Peter Crawford 24-Dec-2011 to reinstate plain text formating that was lost when the Forum posting was converted to Communities

Re: Omitting Holidays weekend from Date function

deleted_user — Wed, 02 Feb 2011 02:48:19 GMT

you guys are awesome 🙂 very nice material here. thanks so much for your time i really appreciate it.