Hello,
I want to extract the number of holidays between two dates and use it in other function. For that, i follow theses steps:
1) I create the table of holiday
data Jour_FERIE;
input Jour_FERIE date9.;
cards;
01JAN2015
01MAY2015
08MAY2015
14MAY2015
25MAY2015
04JUL2015
;
run;
2) I create the macro that extract the number of holidays between two dates
%macro NB_jour_Ferie (date_debut, date_fin);
proc sql ;
select count(*) into :NB from Jour_FERIE where JOUR_FERIE between &date_debut and &date_fin
;
quit;
%mend NB_jour_Ferie;
3) I try to call my macro.
data _null_ ;
a= '29APR2015'd;
b= '15MAY2015'd;
rc= %NB_jour_Ferie(a,b );
put rc;
run;
But i got tis error:
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
13
14 GOPTIONS ACCESSIBLE;
15 data _null_ ;
16 a= '29APR2015'd;
17 b= '15MAY2015'd;
18 rc= %NB_jour_Ferie(a,b );
MLOGIC(NB_JOUR_FERIE): Début de l'exécution.
MLOGIC(NB_JOUR_FERIE) : Le paramètre DATE_DEBUT a la valeur a
MLOGIC(NB_JOUR_FERIE) : Le paramètre DATE_FIN a la valeur b
NOTE: Line generated by the invoked macro "NB_JOUR_FERIE".
18 proc sql ; select count(*) into :NB from Jour_FERIE where JOUR_FERIE between &date_debut and &date_fin ;
___ _____
22 395
76
MPRINT(NB_JOUR_FERIE): proc sql ;
MPRINT(NB_JOUR_FERIE): select count(*) into :NB from Jour_FERIE where JOUR_FERIE between a and b ;
MLOGIC(NB_JOUR_FERIE) : %PUT &NB
WARNING: Apparent symbolic reference NB not resolved.
&NB
NOTE: Line generated by the invoked macro "NB_JOUR_FERIE".
18 quit;
____
180
MPRINT(NB_JOUR_FERIE): quit;
MLOGIC(NB_JOUR_FERIE) : Fin de l'exécution.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,
LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.
ERROR 395-185: Opening parenthesis for SELECT/WHEN expression is missing.
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 180-322: Statement is not valid or it is used out of proper order.
Can you help me please to solve my problem?
Hi,
Yes, you miss the fundamentals of macros there. Think of it this way, if you copied the code within the macro to the place where you called it from, would it be correct Base SAS code? The answer is no:
data _null_ ;
a= '29APR2015'd;
b= '15MAY2015'd;
rc=proc sql ; select count(*) into :NB from Jour_FERIE where JOUR_FERIE between a and b; quit;
put rc;
run;
Can't really suggest much of a good way to do this as the concept of what you are trying to acheive is wrong to me - for instance why the macro in the first place:
proc sql;
select count(*)
into :NB
from Jour_FERIE
where Jour_FERIE between '29APR2015'd and '15MAY2015'd;
quit;
%put &NB.;
This does what you have asked in your example. If you need to update certain tables with this information, then look at update in either SQL or SAS. Also , you can just create a new table with this data.
Hello,
thanks.
My first goal is to calculate the number of working days between two dates. For that, i extract at first the number of holidays between two date in my macro because i can't use proc sql in my sas function.
proc fcmp outlib=work.funcs.test ;
function DELAI(date_debut, date_fin);
a = run_macro('NB_jour_Ferie',date_debut, date_fin );
c= datdif(date_debut, date_fin, 'act/act');
return (c-a);
endsub;
This would be much easier if use the SAS features designed for the purpose.
Look up the SAS system option INTERVALDS you won't need PROC FCMP or RUN_MACRO or any of that.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.