Use proc sql in macro

[deh_asma]

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?

[RW9]

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.

[deh_asma]

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;

[data_null__]

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.