help with dates and merging please

[Josie1]

Help, need to get these holidays indicated on my main calendar with all of the days. bonus points if you can get election day to be the first tuesday unless the first of the month is a tuesday.

i want a binary variable for holidays. i plan to drop the holidays then drop the weekends and have left a data set of the work week. then i need to add two more variables each that count to 21 and restart from a macro start date. creating a route schedule if anyone is familiar

thanks so much!

data CalendarALLDAYS;
do date=&begin to &end; format date date9.;
output;
end;
run;
data CalendarALLDAYS;
set CalendarALLDAYS;
month=month(date);
day=day(date);
dayofweek=weekday(date);
run;

%macro hols;
data holidaylist;
%DO YEAR=2000 %to 2050;
/* 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*/

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.;
date= NYD_HOL;
format date date9.;

/*MLK*/
MLK = holiday('MLK', &year);
format MLK date9.;
date= MLK;

/*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 NYD_HOL into :NYD_HOL_DATES separated by ","
from HOLIDAYLIST;
quit;
%put List of name: &NYD_HOL_DATES.;

proc sql noprint;
select MLK into :MLK_DATES separated by ","
from HOLIDAYLIST;
quit;

proc sql noprint;
select MEMORIAL into :MEMORIAL_DATES separated by ","
from HOLIDAYLIST;
quit;
*%put List of name: &MEMORIAL_DATES.;

proc sql noprint;
select IND_HOL into :IND_HOL_DATES separated by ","
from HOLIDAYLIST;
quit;

proc sql noprint;
select LABOUR into :LABOUR_DATES separated by ","
from HOLIDAYLIST;
quit;

proc sql noprint;
select THANKSGIVING into :THANKSGIVING_DATES separated by ","
from HOLIDAYLIST;
quit;

proc sql noprint;
select BLACKFRIDAY into :BLACKFRIDAY_DATES separated by ","
from HOLIDAYLIST;
quit;

proc sql noprint;
select XMASEVE_HOL into :XMASEVE_HOL_DATES separated by ","
from HOLIDAYLIST;
quit;

proc sql noprint;
select XMAS_HOL into :XMAS_HOL_DATES separated by ","
from HOLIDAYLIST;
quit;

format companyholidays date9.;
%let companyHOLIDAYS= &NYD_HOL_DATES, &MLK_DATES, &MEMORIAL_DATES, &IND_HOL_DATES, &LABOUR_DATES, &THANKSGIVING_DATES, &BLACKFRIDAY_DATES, &XMASEVE_HOL_DATES, &XMAS_HOL_DATES;
%put List of name: &companyHOLIDAYS.;

DATA CalendarALLDAYS;
SET CalendarALLDAYS;
IF date in &companyholidays then hol=1; else hol=0;
run;

[PaigeMiller]

I have a suggestion: perhaps you should use the built-in HOLIDAY function. SAS has already done the hard work, so you don't have to.

[Josie1]

Hi Paige thanks for your reply. I am using some of the sas holidays but I am also making some of my own because of how my office celebrates observed holidays. I need to merge my list of holidays back to the calendaralldays table. The last part of the code at the end, I need that company holidays list to convert into 1 binary variable from the list of dates

[ballardw · Accepted solution]

You may want to look at the documentation for Proc DATEKEYS.

It shows creating custom "Holiday" date descriptions. The examples use things like Super Bowl Sunday or New Years Eve (example 3). Add the "standard" holidays you want.

Example 4 for the procedure shows a "filter" setting flags similar to what you are doing by using the created data set with the system option EVENTDS= pointing to that set. Which becomes the "holiday" data set used by procedures like Holiday, HolidayName etc.

[Josie1]

this has promise. I think my whole issue is my dates are not the right format. if you look on the log where i printed the companyholidays macro variable my dates look like 

%put List of name: &companyHOLIDAYS.;
List of name:
31DEC1999,01JAN2001,01JAN2002,01JAN2003,01JAN2004,31DEC2004,02JAN2006,01JAN2007,01JAN2008,01JA
N2009,01JAN2010,31DEC2010,02JAN2012,01JAN2013,01JAN2014,01JAN2015,01JAN2016,02JAN2017,01JAN201
8,01JAN2019,01JAN2020,01JAN2021,31DEC2021,02JAN2023,01JAN2024,01JAN2025,01JAN2026,01JAN2027,31
DEC2027,01JAN2029,01JAN2030,01JAN2031,01JAN2032,31DEC2032,02JAN2034,01JAN2035,01JAN2036,01JAN2
037,01JAN2038,31DEC2038,02JAN2040,01JAN2041,01JAN2042,01JAN2043,01JAN2044,02JAN2045,01JAN2046,
01JAN2047,01JAN2048,01JAN2049,31DEC2049,
17JAN2000,15JAN2001,21JAN2002,20JAN2003,19JAN2004,17JAN2005,16JAN2006,15JAN2007,21JAN2008,19JA
N2009,18JAN2010,17JAN2011,16JAN2012,21JAN2013,20JAN2014,19JAN2015,18JAN2016,16JAN2017,15JAN201
8,21JAN2019,20JAN2020,18JAN2021,17JAN2022,16JAN2023,15JAN2024,20JAN2025,19JAN2026,18JAN2027,17
JAN2028,15JAN2029,21JAN2030,20JAN2031,19JAN2032,17JAN2033,16JAN2034,15JAN2035,21JAN2036,19JAN2
037,18JAN2038,17JAN2039,16JAN2040,21JAN2041,20JAN2042,19JAN2043,18JAN2044,16JAN2045,15JAN2046,
21JAN2047,20JAN2048,18JAN2049,17JAN2050,

instead of 

proc hpfevents;
     eventdef SuperBowl =               '15JAN1967'D  '14JAN1968'D  '12JAN1969'D  '11JAN1970'D   
                          '17JAN1971'D  '16JAN1972'D  '14JAN1973'D  '13JAN1974'D  '12JAN1975'D
                          '18JAN1976'D  '09JAN1977'D  '15JAN1978'D  '21JAN1979'D  '20JAN1980'D   
                          '25JAN1981'D  '24JAN1982'D  '30JAN1983'D  '22JAN1984'D  '20JAN1985'D
                          '26JAN1986'D  '25JAN1987'D  '31JAN1988'D  '22JAN1989'D  '28JAN1990'D   
                          '27JAN1991'D  '26JAN1992'D  '31JAN1993'D  '30JAN1994'D  '29JAN1995'D
                          '28JAN1996'D  '26JAN1997'D  '25JAN1998'D  '31JAN1999'D  '30JAN2000'D   
                          '28JAN2001'D  '03FEB2002'D  '26JAN2003'D  '01FEB2004'D  '06FEB2005'D
                          '05FEB2006'D  '04FEB2007'D  '03FEB2008'D  '01FEB2009'D  '07FEB2010'D
                          '06FEB2011'D  '05FEB2012'D  '03FEB2013'D  '02FEB2014'D  '01FEB2015'D
                          '07FEB2016'D  '05FEB2017'D  '04FEB2018'D  '03FEB2019'D  '02FEB2020'D 
                          '07FEB2021'D  '06FEB2022'D  '05FEB2023'D  '04FEB2024'D  '02FEB2025'D 
                         / PULSE=DAY ;                                        
     eventdata out=superbowl condense;
     eventdata out=superbowl_full;
run;     
title 'Results of HPFEVENTS - Condensed';
proc print data=superbowl;
run;

[Patrick]

Did you already look into creating a custom interval that then would allow you to use SAS calendar functions like intnx() and intck()?

Example 2 under this docu link might get you close for what you need.

[Josie1]

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);