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;
Try this:
proc sql;
create table test1 as
select distinct a,"&date_start." as temp
from a;
quit;
*******************************************************************
Or
%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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.