Using date macro variable in PROC SQL

Question

I am having trouble getting a macro variable to work correctly in PROC SQL and it doesn't make sense to me.

First, if I generate a query like this:

PROC SQL;
SELECT
   a.*
   ,'31MAR2016' As EVAL_DATE format=date09.
FROM
   myTable a
;

it works as expected and puts a date field at the end of the table.

Then, if I do this:

%Let testDate = '31MAR2016'd;
%put &testDate;

PROC SQL;
SELECT
   a.*
   ,&testDate As EVAL_DATE format=date09.
FROM
   myTable a
;

this again runs properly, with the log window showing the value of:

'31MAR2016'd

But, if I do this:

%Let Eval_Date = %sysfunc(intnx (month,%sysfunc(inputn(201603,yymmn6.)) ,0,E),date09.);
%Let Eval_date_test = %str(%')&Eval_Date.%str(%')d;
%Put Eval_date_test;

PROC SQL;
SELECT
   a.*
   ,&Eval_date_test As EVAL_DATE format=date09.
FROM
   myTable a
;

SAS stops running with the error;

"ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~.

ERROR 200-322: The symbol is not recognized and will be ignored."

The log displays the value of &Eval_date_test to be the same '31MAR2016'd as it was in the second example. Note that I created the variable in two steps for clarity, but the same thing happens if you create it in one step.

In case it matters, I am running SAS Enterprise Guide 6.1

Why doesn't this work?

Why are you using macro quoted single quotes? Why not just use normal double quotes? %let Eval_Date = "%sysfunc(intnx (month,%sysfunc(inputn(201603,yymmn6.)) ,0,E),date9)"d; — Tom
@Tom Because I didn't know about the differences between the single and double quotes. So the single quotes are for macros and are used to delimit strings whereas the double quotes are just treated as regular characters in the context of macros? — OpiesDad
SAS doesn't care which you use., but text inside of single quotes are not evaluated for macro triggers. So '&eval_date' does not resolve the macro variable reference and "&eval_date" does. — Tom

DomPazz DomPazz · Accepted Answer · 2016-04-06T18:00:15

This has to do with how the macro is being dereferenced with the %str() macro. Try the %unquote() macro:

PROC SQL;
SELECT
   a.*
   , %unquote(&Eval_date_test) As EVAL_DATE format=date09.
FROM
   sashelp.cars a
;
quit;

http://support.sas.com/documentation/cdl/en/mcrolref/67912/HTML/default/viewer.htm#p1f5qisx8mv9ygn1dikmgba1lmmu.htm

Using date macro variable in PROC SQL

3 Answers