Invalue not working

[deleted_user]

Im working on a member which i want to incorperate into many of my jobs, but im having an issue with the following

What it is suppost to do is omit weekends and holidays.
and then display the calculated business days - 2

so far its saying the feb 18th is the 49th business day, which it should be 48th.

it seems as if its not deducting the holidays.

Code:

proc format ;

invalue holiday '03JAN2011'd,'21FEB2011'd,'22APR2011'd,'23MAY2011'd,
'01JUL2011'd,'01AUG2011'd,'05SEP2011'd,'10OCT2011'd,'11NOV2011'd,
'25DEC2011'd,'26DEC2011'd = 1
other=0;

run;



data dates2011; /*Get Current Date For Testing */
date = "&SYSDATE9"d;
format date date9.;

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,11.),holiday.) then do;
busday_2before = busday_2before-1;
goto loop; /* make sure not Saturday, sunday, or holiday, again */
end;
format busday_2before date9.;
B2B4 = input(put(busday_2before,julian7.),8.);
run;

[Reeza]

I'd highly recommend creating a date 'dimension' or lookup table and working of off that.

Some fields you could include are:

date weekdaynum isholiday dayofyear workday quarter period year
01Jan2011 7 1 1 0 1 1 2011
02Jan2011 1 0 2 0 1 1 2011
....
02Feb2011 4 0 33 22 1 2 2011

Then use the table above as a lookup table for other programs. You'll use it a lot if you do period style reporting.

SAS also has a holidays function so if you're in the US you may want to see if the holidays are already programmed into that function.

[Reeza]

The following macro is kind of what I use to create a date dimension with the workdays added in and the dates you used. According to my dataset, feb 18 is the 49th day in the year and the 34th workday not 48th. January has 20 days and Feb has to have less than 20 so not sure how you came up with 48 days.


%MACRO DATE_DIMENSION(startdate=, enddate=, outfil=);
data &outfil;

retain number_workdays;

do _n_ = &startdate to &enddate;
date_id + 1;
date = _n_;
format date mmddyy10.;
month = month(date);
calendar_month = year(date)*100+month(date);
day = day(date);
year = year(date);
quarter = mod(qtr(date)+2,4)+1;
length fiscal_year $10.;
if quarter in (2, 3,4) then fiscal_year=compress(cat(year ,"/",year+1));
else if quarter=1 then fiscal_year=compress(cat(year-1,"/",year));
format fiscal_year $10.;
length fiscal_year $10.;

dayofweek=weekday(date);

if dayofweek >= 2 and dayofweek<=6 then workday=1; else workday=0;

if date in ('03JAN2011'd,'21FEB2011'd,'22APR2011'd,'23MAY2011'd,
'01JUL2011'd,'01AUG2011'd,'05SEP2011'd,'10OCT2011'd,'11NOV2011'd,
'25DEC2011'd,'26DEC2011'd ) then isholiday=1; else isholiday=0;

if isholiday then workday=0;

if day=1 and month=1 then number_workdays=0;

number_workdays+workday;



output;
end;
run;
%MEND DATE_DIMENSION;


%date_dimension(startdate='01jan2011'd, enddate='01jan2012'd,outfil=date_dimension)