Hi SAS Experts,
I am trying to pass datetime value into a sas query and its not working for me.
here is my query and any help is appreciated.
MACRO:
%let today = %sysfunc(today());
%let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,-2)),0,0,0));
%put &NEXT_DAY_1;
%let x= %sysfunc(putn(&NEXT_DAY_1, datetime.));
%put &x;
O/P:
22SEP21:00:00:00
SAS Query
this is where i am passing the macro date.
WHERE t1.CALENDAR_WK_ENDING_DATE = &x;
ERROR:
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "X".
80 18SEP21:00:00:00
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
~=.
ERROR 76-322: Syntax error, statement will be ignored.
it takes this format for some reason.
'13Sep2021:0:0:0'dt;
@PrudhviB wrote:
Hi SAS Experts,
I am trying to pass datetime value into a sas query and its not working for me.
here is my query and any help is appreciated.
MACRO:
%let today = %sysfunc(today());
%let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,-2)),0,0,0));
%put &NEXT_DAY_1;%let x= %sysfunc(putn(&NEXT_DAY_1, datetime.));
%put &x;O/P:
22SEP21:00:00:00
SAS Query
this is where i am passing the macro date.
WHERE t1.CALENDAR_WK_ENDING_DATE = &x;
ERROR:
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "X".
80 18SEP21:00:00:00
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
~=.ERROR 76-322: Syntax error, statement will be ignored.
it takes this format for some reason.
'13Sep2021:0:0:0'dt;
The code you use:
WHERE t1.CALENDAR_WK_ENDING_DATE = &x;
Would resolve to
WHERE t1.CALENDAR_WK_ENDING_DATE = 22SEP21:00:00:00;
Which is not legal syntax and throws the error.
There is NO reason to use the formatted value.
Use
WHERE t1.CALENDAR_WK_ENDING_DATE = &NEXT_DAY_1;
The value would resolve to the numeric datetime value and not throw any error.
'13Sep2021:0:0:0'dt;
works because that is a datetime literal. And if you use similar code with the macro and examine that datetime literal the numeric values are the same: (running on 24Sep2021)
461 %let today = %sysfunc(today()); 462 463 %let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,-2)),0,0,0)); 464 %put Nex_date is: &NEXT_DAY_1; Nex_date is: 1947888000 465 466 data _null_; 467 x=intnx('dtday', '24Sep2021:0:0:0'dt,-2); 468 put x= ; 469 run; x=1947888000
An example of why formatting date, time or datetime values in macro variables in code for manipulations is not a good idea. Use the numeric value and keep the formatted values for text like titles and file names that people read.
Show us the entire macro and PROC SQL code.
Show us the entire LOG from running this code.
@PrudhviB wrote:
Hi SAS Experts,
I am trying to pass datetime value into a sas query and its not working for me.
here is my query and any help is appreciated.
MACRO:
%let today = %sysfunc(today());
%let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,-2)),0,0,0));
%put &NEXT_DAY_1;%let x= %sysfunc(putn(&NEXT_DAY_1, datetime.));
%put &x;O/P:
22SEP21:00:00:00
SAS Query
this is where i am passing the macro date.
WHERE t1.CALENDAR_WK_ENDING_DATE = &x;
ERROR:
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "X".
80 18SEP21:00:00:00
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
~=.ERROR 76-322: Syntax error, statement will be ignored.
it takes this format for some reason.
'13Sep2021:0:0:0'dt;
The code you use:
WHERE t1.CALENDAR_WK_ENDING_DATE = &x;
Would resolve to
WHERE t1.CALENDAR_WK_ENDING_DATE = 22SEP21:00:00:00;
Which is not legal syntax and throws the error.
There is NO reason to use the formatted value.
Use
WHERE t1.CALENDAR_WK_ENDING_DATE = &NEXT_DAY_1;
The value would resolve to the numeric datetime value and not throw any error.
'13Sep2021:0:0:0'dt;
works because that is a datetime literal. And if you use similar code with the macro and examine that datetime literal the numeric values are the same: (running on 24Sep2021)
461 %let today = %sysfunc(today()); 462 463 %let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,-2)),0,0,0)); 464 %put Nex_date is: &NEXT_DAY_1; Nex_date is: 1947888000 465 466 data _null_; 467 x=intnx('dtday', '24Sep2021:0:0:0'dt,-2); 468 put x= ; 469 run; x=1947888000
An example of why formatting date, time or datetime values in macro variables in code for manipulations is not a good idea. Use the numeric value and keep the formatted values for text like titles and file names that people read.
i just feel so dumb, how could i miss such a simple thing.
but thanks a ton you just saved a lot of time for me. Great work!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: