topic Re: #Unable to append Date Macro Variable as a constant in PROC SQL in SAS Programming

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

RohitV — Mon, 29 Dec 2014 04:42:35 GMT

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;

;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'

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;

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

mohamed_zaki — Mon, 29 Dec 2014 06:28:10 GMT

Try this:

proc sql;

create table test1 as

select distinct a,"&date_start." as temp

from a;

quit;

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

remove %nrbquote
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;

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

Peter_C — Mon, 29 Dec 2014 08:28:14 GMT

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