BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kevpieris
Fluorite | Level 6

I need to identify the previous working day and if the previous working day is a holiday then to identify the day before. I have input the holidays as a list. If the previous date is matching the holiday list then the previous working day should be -1 day. 

Please help... 
e.g 
Today is Monday (11DEC2023), 
Previous working day Friday  (08DEC2023),
Day Before Previous working day Thursday (07DEC2023)

I need to get Thursday date. 

===Code===

%let current_date = %sysfunc(today(),yymmddn8.);
%let holiday_list = 08DEC2023 25DEC2023;
/* Function to check if a date is in the holiday list */
%macro is_holiday(date);
   %local i holiday;
   %let holiday = 0;
   %do i = 1 %to %sysfunc(countw(&holiday_list));
       %if "&date." = %sysfunc(inputn(%scan(&holiday_list, &i.), yymmdd8.)) %then %do;
           %let holiday = 1;
           %leave;
       %end;
   %end;
&holiday
%mend;
/* Calculate the previous working day and adjust for holidays */
data _null_;
   /* Convert the current date to SAS date format */
   current_date_sas = input("&current_date.", yymmdd8.);
   /* Initialize variables */
   previous_workday = current_date_sas;
   i = 1;
   /* Subtract days until a working day (Mon to Fri) is found */
   do while (weekday(intnx('day', previous_workday, -i)) in (1, 7) or %is_holiday(intnx('day', previous_workday, -i)));
       i = i + 1;
   end;
   /* Calculate the previous working day considering holidays */
   previous_workday = intnx('day', previous_workday, -i);
   /* Check if the result is a holiday and adjust further */
   if %is_holiday(previous_workday) then
       previous_workday = intnx('day', previous_workday, -2);
   /* Format the result as a date and store it in a macro variable */
   call symput('previous_workday', put(previous_workday, date9.));
run;
/* Display the result in the log */
%put Previous working day is &previous_workday.;
1 ACCEPTED SOLUTION

Accepted Solutions
whymath
Lapis Lazuli | Level 10

The logic of your code is right, just need to move forward one step: You are trying to using a function-style macro on a normal variable in data step, which is the root of problem, what you really need is a function, not a macro.

%let current_date = %sysfunc(today(),yymmddn8.);
%let holiday_list = 08DEC2023 25DEC2023;
/* Function to check if a date is in the holiday list */
proc fcmp outlib=work.funcs.date;
  function is_holiday(date,holiday_list$);
    holiday=0;
    do i=1 to countw(holiday_list);
      if date=input(scan(holiday_list, i), date9.) then do;
        holiday=1;
        leave;
      end;
    end;
    return(holiday);
  endfunc;
run;

option cmplib=work.funcs;
/* Calculate the previous working day and adjust for holidays */
data _null_;
   /* Convert the current date to SAS date format */
   current_date_sas = input("&current_date.", yymmdd8.);
   /* Initialize variables */
   previous_workday = current_date_sas;
   i = 1;
   /* Subtract days until a working day (Mon to Fri) is found */
   do while (weekday(intnx('day', previous_workday, -i)) in (1, 7) or is_holiday(intnx('day', previous_workday, -i),"&holiday_list."));
       i = i + 1;
   end;
   /* Calculate the previous working day considering holidays */
   previous_workday = intnx('day', previous_workday, -i);
   /* Check if the result is a holiday and adjust further */
   if is_holiday(previous_workday,"&holiday_list.") then
       previous_workday = intnx('day', previous_workday, -2);
   /* Format the result as a date and store it in a macro variable */
   call symput('previous_workday', put(previous_workday, date9.));
run;
/* Display the result in the log */
%put Previous working day is &previous_workday.;

And surely you can use intnx('workday',...) to simplify your code.

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

From now on, ALWAYS (that's 100% of the time, no exceptions) when you have to do arithmetic or logical operations on dates (like finding previous date, or comparing one date to another), you need to use valid SAS numeric dates, which are the number of days since 01JAN1960. Do not work with dates as strings like 08DEC2023 that you then have to pull apart and perform your own arithmetic on, and combine in some meaningful way.

 

SAS has already done the hard work by providing functions, formats and informats that make handling dates as valid numeric SAS dates very very easy, so you don't have to figure out how to handle them yourself. The INTNX function, with the WEEKDAY time interval and -1 as the increment, finds the previous weekday, it knows that the previous weekday from a Monday is the Friday before and does the arithmetic for you.

 

This code illustrates that

 

%let current_date = %sysfunc(today()); /* Today is 11DEC2023, the integer value for this date is 23355 */
%let holiday_list = 08DEC2023 25DEC2023;
/* Function to check if a date is in the holiday list */
%macro is_holiday(date);
   %let holiday = 0;
   %do i = 1 %to %sysfunc(countw(&holiday_list));
       %let this_holiday=%scan(&holiday_list,&i,%str( )); /* Find the i-th holiday from the list in &holiday_list */
       %let this_holiday=%sysfunc(inputn(&this_holiday,date9.)); /* Convert to actual numeric SAS date value, which for 08DEC2023 is 22352 */
       %let prev_working_date=%sysfunc(intnx(weekday,&date,-1)); /* Use INTNX with the WEEKDAY interval to find the previous weekday */
       /* &DATE is 11DEC2023, which is 23355; &prev_working_date is the previous Weekday which is Friday which is 08DEC2023 which is 23352 */
       %if &prev_working_date = &this_holiday %then %let holiday = 1; /* Is holiday the previous weekday? */
       %let prev_working_date=%eval(&prev_working_date-&holiday);
       %let human_readable_date=%sysfunc(putn(&prev_working_date,date9.));
       %put &=i &=date &=this_holiday &=prev_working_date &=holiday &=human_readable_date;
       %if &holiday=1 %then %goto finish;
   %end;
   %finish:
%mend;
%is_holiday(&current_date)

 

 

Only if you need dates that are readable by humans would you format the values. So this line in the code produces a human readable date, for example, 07DEC2023

 

 

%let human_readable_date=%sysfunc(putn(&prev_working_date,date9.));

 

--
Paige Miller
PaigeMiller
Diamond | Level 26

Adding, with the %INM macro, the code doesn't even require loops. This is the data step IN function, converted to work on macro variables

 

%macro inm(slist,s);
    /* SAS Macro %inm to see if &s is contained in a string or list &slist                 */
    /* Borrowed from https://groups.google.com/forum/#!topic/comp.soft-sys.sas/fWcSDgg11tE */
    %if %sysfunc(indexw(&slist,&s)) gt 0 %then 1 ;
    %else 0;
%mend;

%let current_date = %sysfunc(today()); /* Today is 11DEC2023, the integer value for this date is 23355 */
%let holiday_list = 25DEC2023 08DEC2023 ;
/* Function to check if a date is in the holiday list */
%macro is_holiday(date);
   %let prev_working_date=%sysfunc(intnx(weekday,&date,-1)); /* Use INTNX with the WEEKDAY interval to find the previous weekday */
   /* &DATE is 11DEC2023, which is 23355; &prev_working_date is the previous Weekday which is Friday which is 08DEC2023 which is 23352 */
   %if %inm(&holiday_list,%sysfunc(putn(&prev_working_date,date9.))) %then %let holiday=1; /* Is holiday the previous weekday? */
   %let prev_working_date=%eval(&prev_working_date-&holiday);
   %let human_readable_date=%sysfunc(putn(&prev_working_date,date9.));
   %put &=date &=prev_working_date &=human_readable_date &=holiday ;
%mend;
%is_holiday(&current_date)
--
Paige Miller
kevpieris
Fluorite | Level 6

Hi Miller, 
I am getting this error when I run the code below.

 

WARNING: Apparent symbolic reference HOLIDAY not resolved.
WARNING: Apparent symbolic reference HOLIDAY not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       23355-&holiday 
ERROR: The macro IS_HOLIDAY will stop executing.

 

 

kevpieris
Fluorite | Level 6

Hi Miller, 

I tried this today, the result was 11DEC2023. However, I added 11DEC2023 as a holiday and the code picked as 10DEC2023, instead of 07DEC2023. 

%let current_date = %sysfunc(today()); /* Today is 11DEC2023, the integer value for this date is 23355 */
%let holiday_list = 08DEC2023 11DEC2023 25DEC2023; /*added 11DEC2023 to test it out*/
/* Function to check if a date is in the holiday list */
%macro is_holiday(date);
   %let holiday = 0;
   %do i = 1 %to %sysfunc(countw(&holiday_list));
       %let this_holiday=%scan(&holiday_list,&i,%str( )); /* Find the i-th holiday from the list in &holiday_list */
       %let this_holiday=%sysfunc(inputn(&this_holiday,date9.)); /* Convert to actual numeric SAS date value, which for 08DEC2023 is 22352 */
       %let prev_working_date=%sysfunc(intnx(weekday,&date,-1)); /* Use INTNX with the WEEKDAY interval to find the previous weekday */
       /* &DATE is 11DEC2023, which is 23355; &prev_working_date is the previous Weekday which is Friday which is 08DEC2023 which is 23352 */
       %if &prev_working_date = &this_holiday %then %let holiday = 1; /* Is holiday the previous weekday? */
       %let prev_working_date=%eval(&prev_working_date-&holiday);
       %let human_readable_date=%sysfunc(putn(&prev_working_date,date9.));
       %put &=i &=date &=this_holiday &=prev_working_date &=holiday &=human_readable_date;
       %if &holiday=1 %then %goto finish;
   %end;
   %finish:
%mend;
%is_holiday(&current_date)

%PUT &prev_working_date;
SASKiwi
PROC Star

Your latest program worked fine for me:

28         %let current_date = 23355;
29         %let holiday_list = 08DEC2023 11DEC2023 25DEC2023; /*added 11DEC2023 to test it out*/
30         /* Function to check if a date is in the holiday list */
31         %macro is_holiday(date);
32            %let holiday = 0;
33            %do i = 1 %to %sysfunc(countw(&holiday_list));
34                %let this_holiday=%scan(&holiday_list,&i,%str( )); /* Find the i-th holiday from the list in &holiday_list */
35                %let this_holiday=%sysfunc(inputn(&this_holiday,date9.)); /* Convert to actual numeric SAS date value, which for
35       ! 08DEC2023 is 22352 */
36                %let prev_working_date=%sysfunc(intnx(weekday,&date,-1)); /* Use INTNX with the WEEKDAY interval to find the
36       ! previous weekday */
37                /* &DATE is 11DEC2023, which is 23355; &prev_working_date is the previous Weekday which is Friday which is
37       ! 08DEC2023 which is 23352 */
38                %if &prev_working_date = &this_holiday %then %let holiday = 1; /* Is holiday the previous weekday? */
39                %let prev_working_date=%eval(&prev_working_date-&holiday);
40                %let human_readable_date=%sysfunc(putn(&prev_working_date,date9.));
41                %put &=i &=date &=this_holiday &=prev_working_date &=holiday &=human_readable_date;
42                %if &holiday=1 %then %goto finish;
43            %end;
44            %finish:
45         %mend;
46         %is_holiday(&current_date)
I=1 DATE=23355 THIS_HOLIDAY=23352 PREV_WORKING_DATE=23351 HOLIDAY=1 HUMAN_READABLE_DATE=07DEC2023
47         

 

whymath
Lapis Lazuli | Level 10

The logic of your code is right, just need to move forward one step: You are trying to using a function-style macro on a normal variable in data step, which is the root of problem, what you really need is a function, not a macro.

%let current_date = %sysfunc(today(),yymmddn8.);
%let holiday_list = 08DEC2023 25DEC2023;
/* Function to check if a date is in the holiday list */
proc fcmp outlib=work.funcs.date;
  function is_holiday(date,holiday_list$);
    holiday=0;
    do i=1 to countw(holiday_list);
      if date=input(scan(holiday_list, i), date9.) then do;
        holiday=1;
        leave;
      end;
    end;
    return(holiday);
  endfunc;
run;

option cmplib=work.funcs;
/* Calculate the previous working day and adjust for holidays */
data _null_;
   /* Convert the current date to SAS date format */
   current_date_sas = input("&current_date.", yymmdd8.);
   /* Initialize variables */
   previous_workday = current_date_sas;
   i = 1;
   /* Subtract days until a working day (Mon to Fri) is found */
   do while (weekday(intnx('day', previous_workday, -i)) in (1, 7) or is_holiday(intnx('day', previous_workday, -i),"&holiday_list."));
       i = i + 1;
   end;
   /* Calculate the previous working day considering holidays */
   previous_workday = intnx('day', previous_workday, -i);
   /* Check if the result is a holiday and adjust further */
   if is_holiday(previous_workday,"&holiday_list.") then
       previous_workday = intnx('day', previous_workday, -2);
   /* Format the result as a date and store it in a macro variable */
   call symput('previous_workday', put(previous_workday, date9.));
run;
/* Display the result in the log */
%put Previous working day is &previous_workday.;

And surely you can use intnx('workday',...) to simplify your code.

kevpieris
Fluorite | Level 6

Hey,
Thank you very much; it worked!
I even tried adding 11DEC2023 as a holiday to see the outcome, and I got 07DEC2023.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 1015 views
  • 3 likes
  • 5 in conversation