topic Re: Use proc sql in macro in SAS Programming

Use proc sql in macro

deh_asma — Thu, 07 May 2015 10:26:34 GMT

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

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

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?

Re: Use proc sql in macro

RW9 — Thu, 07 May 2015 10:57:13 GMT

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.

Re: Use proc sql in macro

deh_asma — Thu, 07 May 2015 11:17:29 GMT

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;

Re: Use proc sql in macro

data_null__ — Thu, 07 May 2015 11:50:41 GMT

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.