/*This uses the SAS "holiday and weekend" program, then checks your dates/times and fixes them to conform to your business rules */ /*It is just four simple queries once you get the holiday file made */ /*Runs in less than a second. */ /*Good luck on your project! */ /*Create The Calendar File*/ /*Obtained directly from the SAS Knowledge Base see file http://support.sas.com/kb/26/044.html */ /***************************************************************************/ /* This sample creates three data sets, HOLIDAYS, WEEKENDS, and ALLDAYS */ /* which are combined to create CALENDAR. If you must often calculate the */ /* number of business days between dates, you might prefer to make */ /* CALENDAR a permanent data set rather than recreate it each time. */ /* */ /* Two methods are provided below which use CALENDAR and a second data set */ /* with multiple start and end dates to determine the number of business */ /* dates between STARTDT and STOPDT. */ /***************************************************************************/ /* If you choose, you can edit your calendar ranges here. No other edits */ /* are needed to the steps creating the data set CALENDAR. */ %let start='01jan1960'd; %let stop='01jan2060'd; /* Create a date set for holidays. Adjust to fit your company's needs. */ /* Note logic is illustrated for 'static' holidays and 'observed' holidays. */ data holidays; length type $25; do year=year(&start) to year(&stop); /* example of 'observed' holiday logic */ type='New Years Day Observed'; dt=MDY(1,1,YEAR); FDOY=dt; if weekday(dt)=1 then dt=dt+1; else if weekday(dt)=7 then dt=mdy(12,31,year-1); output; /* example of static holiday logic */ type='Martin Luther King Day'; dt=intnx('week.2',fdoy,(weekday(fdoy) ne 2)+2); output; type="Presidents Day"; fdo_feb=intnx('month',fdoy,1); dt=intnx('week.2',fdo_feb,(weekday(fdo_feb) ne 2)+2); output; type='Memorial Day'; fdo_may=intnx('month',fdoy,4); dt=intnx('week.2',fdo_may,(weekday(fdo_may) in (1,7))+4); output; type='Independence Day Observed'; dt=MDY(7,4,YEAR); if weekday(dt)=1 then dt=dt+1; else if weekday(dt)=7 then dt=dt-1; output; type='Labor Day'; fdo_sep=intnx('month',fdoy,8); dt=intnx('week.2',fdo_sep,(weekday(fdo_sep) ne 2)); output; type='Election Day'; fdo_nov=intnx('month',fdoy,10); dt=intnx('week.3',fdo_nov,1); output; type='Veterans Day Observed'; dt=MDY(11,11,YEAR); if weekday(dt)=1 then dt=dt+1; else if weekday(dt)=7 then dt=dt-1; output; type='Thanksgiving Day'; dt=intnx('week.5',fdo_nov,(weekday(fdo_nov) ne 5)+3); output; type='Christmas Day Observed'; dt=MDY(12,25,YEAR); if weekday(dt)=1 then dt=dt+1; else if weekday(dt)=7 then dt=dt-1; output; end; keep dt type; run; proc sort data=holidays; by dt; run; /* Create a data set of weekends via the WEEKDAY function */ data weekends; length type $25; type='Weekend'; do dt=&start to &stop; if weekday(dt) in (1,7) then output; end; run; /* Create a data set of all the days in the specified date range. */ /* TYPE will have the value 'Workday' for all observations. */ /* Specifying ALLDAYS first in the following MERGE will allow any */ /* date matches from from HOLIDAY or WEEKEND to overwrite the */ /* value of TYPE with the appropriate type of day. */ data alldays; length type $25; type='Workday'; do dt=&start to &stop; output; end; run; data calendar; format dt date9.; merge alldays(in=a) weekends(in=w) holidays(in=h); by dt; run; /* Generate dummy data to use with CALENDAR for testing purposes */ data test; startdt='01nov2014'd; stopdt='30nov2014'd; output; startdt='01jan2014'd; stopdt='10jul2014'd; output; startdt='20dec2014'd; stopdt='15jan2015'd; output; format startdt stopdt date9.; run; /* Method 1: SQL */ proc sql; create table final_sql as select startdt format=date9., stopdt format=date9., (select count(*) from calendar where dt between stopdt and startdt and type = 'Workday') as workdays from test; quit; proc print data=final_sql; title 'Output from PROC SQL'; run; /* Method 2: DATA step using an INDEX and KEY= */ /* Build index on CALENDAR */ proc datasets library=work nolist; modify calendar; index create dt; quit; data final_idx; set test; workdays=0; /* For each date between STARTDT and STOPDT, check to see if DT is a workday. */ /* If so, increment the new variable WORKDAYS by 1. */ do i=startdt to stopdt; dt=i; /* Look up the current value of DT in CALENDAR using the index on DT */ set calendar key=dt/unique; /* Check return code from search */ select (_iorc_); /* Match found */ when (%sysrc(_sok)) do; if type='Workday' then workdays+1; if i=stopdt then output; end; /* Match not found in master */ when (%sysrc(_dsenom)) do; _ERROR_=0; end; otherwise do; put 'Unexpected ERROR: _iorc_= ' _iorc_; stop; end; end; end; keep startdt stopdt workdays; run; proc print data=final_idx; title "Output from DATA Step"; run; proc datasets lib = work nolist; save calendar; /* Creating a sample file to mimic your specifications*/ data testvariance; format example $30.; example = "1 - A Holiday"; format literal_receipt datetime.; format literal_date date9.; Literal_receipt="01jan2014 09:00:00"dt; Literal_date=datepart(literal_receipt); output; example = "2 - A Weekend"; Literal_receipt="04jan2014 11:00:00"dt; Literal_date=datepart(literal_receipt); output; example = "3 - Early Hours"; Literal_receipt="07jan2014 01:00:00"dt; Literal_date=datepart(literal_receipt); output; example = "4 - Late Hours"; Literal_receipt="14jan2014 21:00:00"dt; Literal_date=datepart(literal_receipt); output; example = "4 - Bump Weekend to Holiday"; Literal_receipt="25may2014 10:00:00"dt; Literal_date=datepart(literal_receipt); output; example = "5 - Observed Xmas on Friday"; Literal_receipt="24dec1999 10:00:00"dt; Literal_date=datepart(literal_receipt); output; run; /* link test file to calendar file for type */ run;PROC SQL; CREATE TABLE WORK.Linked AS SELECT t1.example, t1.literal_date, t1.literal_receipt, t2.dt, t2.type FROM WORK.TESTVARIANCE t1 INNER JOIN WORK.CALENDAR t2 ON (t1.literal_date = t2.dt); QUIT; /* shift according to type, or according to time received */ PROC SQL; CREATE TABLE WORK.Reset_Receipt_DT AS SELECT t1.dt, t1.example, t1.literal_date, t1.literal_date format=weekdatx9. as DayOfWeek, t1.literal_receipt, t1.type, /* Moved_Receipt_DT */ (ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.literal_receipt))=6,intnx("dtday" ,t1.literal_receipt,2,"b")+(8*60*60), ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.literal_receipt)) in (2,3,4,5) ,intnx( "dtday",t1.literal_receipt,1,"b")+(8*60*60), ifn(timepart(t1.Literal_receipt)<(8*60*60), intnx("dtday", t1.literal_receipt,0,"b")+(8*60*60), ifn(timepart(t1.Literal_receipt)>(17*60*60),intnx("dtday",t1.literal_receipt,1,"b")+(8*60*60), ifn(weekday(datepart(t1.Literal_receipt))=7,intnx("dtday",t1.literal_receipt,2,"b")+(8*60*60), ifn(weekday(datepart(t1.Literal_receipt))=1,intnx("dtday",t1.literal_receipt,1,"b")+(8*60*60),t1.literal_receipt))))))) format = datetime. AS Moved_Receipt_DT, datepart(calculated moved_receipt_dt) format=date9. as New_date FROM WORK.LINKED t1; QUIT; /* You have to relink to calendar in order to see if you moved a holiday onto a weekend, and so move again */ PROC SQL; CREATE TABLE WORK.Reset_Receipt_DT AS SELECT t1.DayOfWeek, t1.dt, t1.example, t1.literal_date, t1.literal_receipt, t1.Moved_Receipt_DT, t1.New_date, t2.type FROM WORK.RESET_RECEIPT_DT t1 INNER JOIN WORK.CALENDAR t2 ON (t1.New_date = t2.dt); QUIT; /*This is the final moved date/time */ PROC SQL; CREATE TABLE WORK.Final_out AS SELECT t1.dt, t1.example, t1.literal_date, t1.DayOfWeek, t1.literal_receipt, t1.type, /* Moved_Receipt_DT */ (ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.Moved_Receipt_DT))=6,intnx("dtday" ,t1.Moved_Receipt_DT,2,"b")+(8*60*60), ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.Moved_Receipt_DT)) in (2,3,4,5) ,intnx( "dtday",t1.Moved_Receipt_DT,1,"b")+(8*60*60), ifn(timepart(t1.Moved_Receipt_DT)<(8*60*60), intnx("dtday", t1.Moved_Receipt_DT,0,"b")+(8*60*60), ifn(timepart(t1.Moved_Receipt_DT)>(17*60*60),intnx("dtday",t1.Moved_Receipt_DT,1,"b")+(8*60*60), ifn(weekday(datepart(t1.Moved_Receipt_DT))=7,intnx("dtday",t1.Moved_Receipt_DT,2,"b")+(8*60*60), ifn(weekday(datepart(t1.Moved_Receipt_DT))=1,intnx("dtday",t1.Moved_Receipt_DT,1,"b")+(8*60*60),t1.Moved_Receipt_DT))))))) format = datetime. AS Moved_Receipt_DT_2 FROM WORK.Reset_Receipt_DT t1; QUIT;
... View more
