I need to identify the previous working day in SAS EG

[kevpieris]

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

[JosvanderVelden]

Please have a look at these posts: https://communities.sas.com/t5/SAS-Programming/How-to-find-Nth-business-day-incl-holidays/td-p/44530... & https://communities.sas.com/t5/SAS-Programming/Previous-working-day/td-p/825964.

[PaigeMiller]

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

[PaigeMiller]

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)

[kevpieris]

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]

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]

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 · Accepted solution]

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]

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