#Unable to append Date Macro Variable as a constant in PROC SQL

[RohitV]

I am trying to append a calculated macro variable as a constant column in proc sql but it is showing an error. Below is the code.

%let date_start = %nrbquote('%sysfunc(sum(%sysfunc(today()),-9),yymmdd10.)');

data a;

infile cards;

informat a $2.;

input a ;

cards;

05

07

03

;run;

proc sql;

create table test1 as

select distinct a,&date_start. as temp

from a;

quit;

20         proc sql;

21         create table test1 as

22: LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN where the error has occurred.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,

              AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,

              LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

22         select distinct a,&date_start. as temp

NOTE: Line generated by the macro variable "DATE_START".

22         '2014-12-19'

           _

           22

            _

            200

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.

200: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN where the error has occurred.

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

23         from a;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

24         quit;

[mohamed_zaki]

Try this:

proc sql;

create table test1 as

select distinct a,"&date_start." as temp

from a;

quit;

*******************************************************************

Or

1. remove %nrbquote
2. then if you want your constant to be number _sas date_ or just char, if char then use "&date_start."
   

%let date_start = %sysfunc(sum(%sysfunc(today()),-9),yymmdd10.);

proc sql;

create table test1 as

select distinct a,&date_start. as temp

from a;

quit;

[Peter_C]

Single quotes around dates in that format are important only if you are using explicit pass-through via SAS/ACCESS to a database like teradata. For this query in base SAS double and single quotes are equivalent.

I think the problem with the syntax causing tbose problems in the log is that special (nrbquote) quoting.

So I think you can remove that problem and achieve the string you seek by using %unquote like

select distinct a, %unquote( &start_date) as temp

Of course most of us would add a date constant to a table   differently.... 

but that's not what you asked .....

Merry Christmas

peterC