Receiving a macro result as return value, instead of into a global variable

[lc_isp]

    Hi _ALL_,

I'm facing a strange problem, while I'm trying to understand if a "day" (numeric) is a working day, an (italian) holiday or a weekend.

The weekend part is working as expected:

%macro isWeekEnd(dateToCheck);
	%let isWeekEnd = %sysfunc(weekday(&dateToCheck));
	%if &isWeekEnd eq 1 or &isWeekEnd eq 7 %then %do;
		%let isWeekEnd=1;
	%end;
	%else %do;
		%let isWeekEnd=0;
	%end;
	&isWeekEnd
%mend isWeekEnd;

so that I can write

%if %isWeekEnd(&moggin.) %then %do;

having &moggin = 23300 (which is Tuesday 17 Oct 2023) gives back 0 etc.

I tried the same with an isHolidayIt() macro then:

%macro isHolidayIt(dateToCheck);
    %local LCisHolidayIt;
    %let LCisHolidayIt=0;;
    proc sql noprint;
        select count(*)
        into :LCisHolidayIt
        from Festiv_ITA
        where datan = &dateToCheck;
    quit;
	&LCisHolidayIt
%mend isHolidayIt;

%let festa=%isHolidayIt(23315); /* 1 Nov 2023: holiday */
%put &=festa;

after preparing a Festiv_ITA table with italian holidays for the year (4 variables, 13 rows, with first variable is datan: numeric holiday): if the dateToCheck is holiday it finds 1 record, and finds 0 of them if not, so we have the 0/1 situation again.

All good but I'm not getting back the return value in any way, here's the log:

26         /* DEBUG: _VERBOSE_ */
27         OPTIONS mprint mprintnest mlogic mlogicnest symbolgen syntaxcheck autocorrect fullstimer source stimer threads;
28         
29         %macro isHolidayIt(dateToCheck);
30             %local LCisHolidayIt;
31             %let LCisHolidayIt=0;;
32             proc sql noprint;
33                 select count(*)
34                 into :LCisHolidayIt
35                 from Festiv_ITA
36                 where datan = &dateToCheck;
37             quit;
38         	&LCisHolidayIt.
39         %mend isHolidayIt;
40         
41         %let festa=%isHolidayIt(23315);
MLOGIC(ISHOLIDAYIT):  Beginning execution.
MLOGIC(ISHOLIDAYIT):  Parameter DATETOCHECK has value 23315
MLOGIC(ISHOLIDAYIT):  %LOCAL  LCISHOLIDAYIT
MLOGIC(ISHOLIDAYIT):  %LET (variable name is LCISHOLIDAYIT)
MPRINT(ISHOLIDAYIT):   proc sql noprint;
SYMBOLGEN:  Macro variable DATETOCHECK resolves to 23315
MPRINT(ISHOLIDAYIT):   select count(*) into :LCisHolidayIt from Festiv_ITA where datan = 23315;
MPRINT(ISHOLIDAYIT):   quit;
SYMBOLGEN:  Macro variable LCISHOLIDAYIT resolves to        1
41                  1
                    _
                    180
2                                                          The SAS System                            12:57 Tuesday, October 17, 2023

MPRINT(ISHOLIDAYIT):   1
MLOGIC(ISHOLIDAYIT):  Ending execution.
ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable FESTA resolves to 
42         %put &=festa;
FESTA=
43

Another strange thing (at least to me) is, if I don't put double ";;" before the proc sql, I see in sas log that its code get assigned to the macro variable:

MLOGIC(ISHOLIDAYIT):  %LOCAL  LCISHOLIDAYIT
MLOGIC(ISHOLIDAYIT):  %LET (variable name is LCISHOLIDAYIT)
41         proc sql noprint;         select count(*)         into :LCisHolidayIt         from Festiv_ITA         where datan =
                                     ______
                                     180
41       ! &dateToCheck;     quit;  &LCisHolidayIt.
ERROR 180-322: Statement is not valid or it is used out of proper order.

;; seems fixing it but the first problem remains: how can I "capture" the return value to assign it externally?

Assigning the value internally, to a global variable, works, but it's not what I would like to achieve: if I do that, all the programs which will use such macro will have to define a global which exact matches the one which macro uses, so the macro won't work as a "black box" (which I prefer).

I tried to use the same logic and syntax, into isWeekEnd() and isHolidayIt() and I'm not getting why the second is not working like the first one.

[Reeza]

I think macros is the wrong tool for this job.

You seem to want to implement a custom calendar instead or possibly PROC FCMP which is a function or even a format instead.

[ballardw]

When you put code other than just macro statements, such as your Proc SQL then the usage as a macro "function" is going to break.

Suggestion: Look at the documentation for the Holidayname function.

You can create a data set using the DATEKEYS procedure to provide your additional holidays and then reference that data set with the SAS option EVENTDS= to make a custom list of holidays available to the SAS holiday related functions.

Holidayname, when used correctly will return the name of the holiday or a blank. The Index parameter comes into play when you want a specific version of the holiday. The example in the online help for Holidayname shows use of a holiday that in the US may have multiple observations on the same day (or not if  occurring on Saturday or Sunday).

[Quentin · Accepted solution]

Agree with others, you are better off using the HOLIDAYNAME function.

As a learning exercise, what you are trying to do is create a 'function-style macro'.  The challenge is that a function-style macro can only contain macro language statements, not SAS SAS statements.  

If you had asked this question 11 years ago, the answer would have been "You can't use a PROC step inside a function-style macro."  But 10 years ago Rick Langston decided to add a function, DOSUBL, to SAS, which actually makes this possible.  See: https://support.sas.com/resources/papers/proceedings13/032-2013.pdf.

Basically, you use %SYSFUNC to invoke the DOSUBL function, so the macro definition is still pure macro code.  Then DOSUBL creates a magical 'side-session' which can execute the PROC SQL step.  e.g.:

%macro isHolidayIt(dateToCheck);
    %local rc LCisHolidayIt;
    %let LCisHolidayIt=0;
    %let rc=%sysfunc(dosubl(%nrstr(
      proc sql noprint;
          select count(*)
          into :LCisHolidayIt
          from Festiv_ITA
          where datan = &dateToCheck;
      quit;
    ))) ;
	&LCisHolidayIt.
%mend isHolidayIt;

data Festiv_ITA ;
  input datan ;
  cards ;
23315
;
run ;

%let festa=%isHolidayIt(23315);
%put &=festa ;

%let festa=%isHolidayIt(23314);
%put &=festa ;

Again, not suggesting you use this approach for this problem.  But as you are developing macros, it's helpful to know that DOSUBL provides you with this ability to write function-style macro that executes SAS code.

[lc_isp]

Thank you very much, all, for the quick answers and solutions (I was struggling on that since two days): all answers have been very useful (as always) and yes, I previously saw the EVENTDS thing but, to be honest, I didn't know how to manage it and figured out how to write a small "perpetual calendar" (which gives previous, current, and next year's holidays: 39 obs at all) taking a millisec to update every time I launch my EG project, (while I'm not sure I can "write our local holidays somewhere" into a dataset read via EVENTDS: I probably have to ask This and That department... I'm not sure: it's not that clear, here, who manages what).

So, at last, and despite Quentin's comment ("not suggesting you use this approach for this problem"), his suggestion seems saving my day: I tried it on my Festiv_ITA full calendar and it works as expected there too.

I'm going to apply the same logic to a PrevLabDay() macro too, which is giving the same problem since I enabled it to work with isHolidayIt(): while it was working with isWeekEnd() only it worked like a charm (and now you explained the reason behind).

I was nearly modifying my code, to make it use of &rc global var instead (so that my colleagues, which are working at the data side, can go forth), but I'll implement the DOSUBL suggestion, now, and will give a look into EVENTDS things, later, as it's probably a better/more robust way: even now I'm basing on holiday('EASTER'), which is luckily in common with non-ita-custom holidays (GREAT luck, as it's a variable holiday date, "base" for two other holidays here: all others are fixed ones and can be summarized with 10 datalines).