asked on

SAS Macro help

I have a SAS program that evaluates for Monday,
%let mspdate=%sysfunc(ifc(%sysfunc(weekday( %sysfunc(today()) )) NE 2, today()-1 , today()-3 )) ;

I then use MSPDATE in a query, all runs fine.

Separately , I have the Sas Holiday program , that has a holidate field showing that this past Monday was a holiday.

How can I adapt this logic below by looking at that holiday table ?

If yesterday was a holiday,

%let mspdate=%sysfunc(ifc(%sysfunc(weekday( %sysfunc(today()) )) NE 2, today()-2 , today()-4 )) ;

else

%let mspdate=%sysfunc(ifc(%sysfunc(weekday( %sysfunc(today()) )) NE 2, today()-1 , today()-3 )) ;

Thanks

Ian

The numbers you gave if yesterday was a holiday were not quite correct. However using the same logic flow we get ----

** This goes better in small steps and without if conditions. **;

** first get today and related status - **;

%let today = %sysfunc(today());
%let isMonday = %eval(%sysfunc(weekday( &today)) = 2);
%let isTuesday = %eval(%sysfunc(weekday( &today)) = 3);
%let isWed2Fri = %eval((&weekDay > 3) and (&weekDay < 7));

** then find out if yesterday was a holiday - **;

proc sql noprint;
select unique HoliDate
from Holidays
where (HoliDate = (&today - 1) );
quit;
%let yesterdayWasHoliday = &sqlObs;

T** hen the calculation of mspDate (following your scheme) is trivial; **;

%let mspdate=%eval(
&today - (
1 +
(2*isMonday) +
(&isWed2Fri * &yesterdayWasHoliday) +
(3 * &isTuesday * &yesterdayWasHoliday)
) );

=======================

However this will not give you what I expect you want when there are more than one holiday in a row (except Sunday-Monday).

In some middle eastern countries and in China, there is (for different festivals) five or more holidays in a row.

Additionally we have successive holidays on Good friday - Easter monday as well as Christmas day and Boxing day. All these cases could be a problem for you depending on what you actually need.

;

Bubba Jones

ASKER

Ian - Thanks .

I tried popping this into a program , and I get an error on the 3rd line of the 1st section of code:

%let isWed2Fri = %eval((&weekDay > 3) and (&weekDay < 7));

WARNING: Apparent symbolic reference WEEKDAY not resolved.
WARNING: Apparent symbolic reference WEEKDAY not resolved.
WARNING: Apparent symbolic reference WEEKDAY not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
(&weekDay > 3) and (&weekDay < 7)
WARNING: Apparent symbolic reference WEEKDAY not resolved.
31 %let isWed2Fri = %eval((&weekDay > 3) and (&weekDay < 7));

Also - wondering - Should this result in a single row or no row if no natch since 5/28/2020 is not a holiday in the Holiday table:

/*proc sql noprint;*/
proc sql;

create table test123 as
select unique HoliDate
from rrTeam.Holidays;
where (HoliDate = (&today - 1) );
quit;

A list of holidays through 2026 appeared in my table.

ASKER CERTIFIED SOLUTION

Ian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial