thanks. this gave me the idea that i was able to get to work! posting my whole code if anybody needs to make a route schedule
my solution:
/*directory*/
%let path=K:\networkplace;
%let begin='01JAN2020'd;
%let end='31DEC2050'd;
%let systemsaturday='16Jan2021'd, '14Jan2023'd; /*exceptions + days to keep. list can be expanded*/
%let beginroutes= '01Jan2020'd;
%macro hols;
data holidaylist;
%DO YEAR=2020 %to 2050;
/*NEW YEARS*/
FDOY=MDY(1,1,&YEAR);
NYD = FDOY;
NYD_HOL = NYD;
IF WEEKDAY(NYD) = 1 THEN NYD_HOL=NYD+1;
IF WEEKDAY(NYD) = 7 THEN NYD_HOL=NYD-1;
format NYD_HOL date9.;
/* FDO-prefixed variables are First Day Of... For */
/* example, FDOY is First Day of Year. */
/*NWKDOM(n, weekday, month, year)
where Sunday=1 and Saturday=6 AND N=week of the month from 1-5 AND
N=5 indicates the last week of the month*/
/*MLK*/
MLK = holiday('MLK', &year);
format MLK date9.;
/*Memorial*/
MEMORIAL = holiday('MEMORIAL', &year);
format Memorial date9.;
/* Independence Day */
IND_DAY=MDY(7,4,&YEAR);
IND_HOL=IND_DAY;
IF WEEKDAY(IND_DAY) = 1 THEN IND_HOL=IND_DAY+1;
IF WEEKDAY(IND_DAY) = 7 THEN IND_HOL=IND_DAY-1;
format IND_HOL date9.;
/*Labor*/
LABOUR = holiday('LABOR', &year);
format Labour date9.;
/* Election Day */
/* can fall on first tuesday in Nov unless FOM is tuesday
FDO_NOV=INTNX('MONTH',FDOY,10);
ELEC_DAY=INTNX('WEEK.3',FDO_NOV,1);*/
/*Thanksgiving*/
THANKSGIVING = holiday('THANKSGIVING', &year);
BLACKFRIDAY = holiday('THANKSGIVING', &year)+1;
format THANKSGIVING date9.;
format BLACKFRIDAY date9.;
/* Christmas Day and Christmas Eve */
XMAS_EVE=MDY(12,24,&YEAR);
XMASEVE_HOL=XMAS_EVE;
IF WEEKDAY(XMAS_EVE) = 1 THEN XMASEVE_HOL=XMAS_EVE+1;
IF WEEKDAY(XMAS_EVE) = 7 THEN XMASEVE_HOL=XMAS_EVE-1;
xmasevewkday=weekday(xmas_eve);
format XMASEVE_HOL date9.;
XMAS_DAY=MDY(12,25,&YEAR);
XMAS_HOL=XMAS_DAY;
IF WEEKDAY(XMAS_DAY) = 1 THEN XMAS_HOL=XMAS_DAY+1;
IF WEEKDAY(XMAS_DAY) = 7 THEN XMAS_HOL=XMAS_DAY-1;
IF XMASEVE_HOL = MDY(12,25,&YEAR) THEN XMAS_HOL = MDY(12,26,&YEAR);
xmaswkday=weekday(xmas_day);
format XMAS_HOL date9.;
drop FDOY NYD IND_DAY XMAS_DAY XMAS_EVE;
output;
%end;
RUN;
%mend;
%hols;
proc sql noprint;
select distinct "'"||put(NYD_Hol,date9.)||"'d" into :NYD_HOL_DATES separated by ','
from holidaylist;
quit;
run;
%put List of names: &NYD_HOL_DATES.;
proc sql noprint;
select distinct "'"||put(MLK,date9.)||"'d" into :MLK_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(MEMORIAL,date9.)||"'d" into :MEMORIAL_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(IND_HOL,date9.)||"'d" into :IND_HOL_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(LABOUR,date9.)||"'d" into :LABOUR_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(THANKSGIVING,date9.)||"'d" into :THANKSGIVING_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(BLACKFRIDAY,date9.)||"'d" into :BLACKFRIDAY_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(XMASEVE_HOL,date9.)||"'d" into :XMASEVE_HOL_DATES separated by ','
from holidaylist;
quit;
run;
proc sql noprint;
select distinct "'"||put(XMAS_HOL,date9.)||"'d" into :XMAS_HOL_DATES separated by ','
from holidaylist;
quit;
run;
data CompanyHolidayCalendar;
do date=&begin to &end; format date date9.;
output;
end;
run;
data CompanyHolidayCalendar;
set CompanyHolidayCalendar;
Weekday = weekday(date);
if Weekday in (2,3,4,5,6) then DayType = 'Weekday ';
else if Weekday in (1,7) then DayType = 'Weekend';
if date in (&NYD_HOL_DATES, &MLK_DATES, &MEMORIAL_DATES) then hol= 1;
else if date in (&IND_HOL_DATES, &LABOUR_DATES, &THANKSGIVING_DATES) then hol= 1;
else if date in (&BLACKFRIDAY_DATES, &XMASEVE_HOL_DATES, &XMAS_HOL_DATES) then hol= 1;
else hol=0;
run;
data ReadingDayCalendartemp;
set CompanyHolidayCalendar;
if hol = 1 then delete;
if weekday in (1,7) and date not in (&systemsaturday) then delete;
yr=year(date);
run;
/*%let routes = 21;
proc sort data=readingdaycalendar; by date yr; run;
data readingdaycaltest;
set readingdaycalendar;
by yr;
if first.yr then routestart=1; else
routestart = 1+ mod(_N_ - 1, &routes.);
output;
run;*/
proc sort data=readingdaycalendartemp; by date yr; run;
data readingdaycalendar;
set readingdaycalendartemp;
by yr;
if first.yr then endroute=1; else
if endroute >=21 then endroute= 1; else endroute+1;
*if last.yr AND endroute=1 then delete;
/*sometimes i randomly have a route 1 end on the last business day of the year like in 2020 and 2024
may need these days in the dataset later to get the startroutedt correct can explore outputting to separtate data set and merging back in*/
*startroute=lag1(endroute);
format startroutedt endroutedt date9.;
endroutedt=date;
startroutedt=lag21(endroutedt);
numdays=intck('days', startroutedt, endroutedt);
run;
%macro export(dsn, outfile);
proc export data=&dsn outfile="&path.\&outfile" dbms=excelcs replace; run;
%mend;
%export(holidaylist, companyholidays);
%export(readingdaycalendar, readingdaycalendar);
... View more