BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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 🙂
3 REPLIES 3
advoss
Quartz | Level 8
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;
Peter_C
Rhodochrosite | Level 12

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 ;

  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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3507 views
  • 0 likes
  • 3 in conversation