CODEWITHSUNDEEP
sas
OpiesDad
OpiesDad

Reputation: 3435

Using date macro variable in PROC SQL

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?

Upvotes: 1

Views: 2836

Answers (3)

Tom
Tom

Reputation: 51621

You are working much too hard and just confusing poor old SAS. Instead of using macro quoting just use the right quote characters to begin with. SAS doesn't care which quote characters you use, but text inside of single quotes is not evaluated for macro triggers and text inside of double quotes is. So '&eval_date'd does not resolve the macro variable reference and "&eval_date"d does. 

%let Eval_Date="%sysfunc(intnx(month,%sysfunc(inputn(201603,yymmn6)),0,E),date9)"d;

Upvotes: 2

DomPazz
DomPazz

Reputation: 12465

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

Upvotes: 2

AGC
AGC

Reputation: 101

You're missing a comma after a.*

Upvotes: 0

Related Questions