Passing Datetime into a SAS query using MACROS

[PrudhviB]

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;

[PaigeMiller]

Show us the entire macro and PROC SQL code.

Show us the entire LOG from running this code.

[PrudhviB]

here is the details i have edited some sensitive data.

program:
*/calling date for the last saturday in sas date format;

libname TEMP "/sasdata/Automation_AR/Raj_Bandi/TEMP";
libname DB META library="data-bric";
libname DB2 META library="data-brick2";
%let today = %sysfunc(today());

%let lst_sat = %sysfunc(dhms(%sysfunc(intnx(day,&today,-6)),0,0,0));
%put &lst_sat;

%let x= %sysfunc(putn(&lst_sat, datetime.));
%put &x;



PROC SQL;
CREATE TABLE TEMP.Cascade AS
SELECT *
FROM DB.V_COLN_RSRC_PD_CALL_FACT_H_TST t1
where t1.CALENDAR_WK_ENDING_DATE = &x;
Quit

Log:
1 The SAS System 15:13 Thursday, September 23, 2021

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program 3';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET
5 ! _CLIENTPROJECTPATH='C:\desktop\AppData\Roaming\SAS\EnterpriseGuide\8\AutoRecovery\d6f9ec4c-dd04-46d6-b4ad-1ae86eecbb
5 ! 1e\Project_(Recovered).egp';
6 %LET _CLIENTPROJECTPATHHOST='RBCONALF1TQZ6S';
7 %LET _CLIENTPROJECTNAME='Project_(Recovered).egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HtmlBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 */calling date for the last saturday in sas date format;
29
30 libname TEMP "/sasdata/Automation_AR/Raj_Bandi/TEMP";
NOTE: Libref TEMP was successfully assigned as follows:
Engine: V9
Physical Name: /sasdata/Automation_AR/Raj_Bandi/TEMP
31 libname DB META library="data-brick";
NOTE: Libref DB was successfully assigned as follows:
Engine: META
Physical Name: DB
32 libname DB2 META library="data-brick2";
NOTE: Libref DB2 was successfully assigned as follows:
Engine: META
Physical Name: DB2
33 %let today = %sysfunc(today());
34
35 %let lst_sat = %sysfunc(dhms(%sysfunc(intnx(day,&today,-6)),0,0,0));
36 %put &lst_sat;
1947542400
37
38 %let x= %sysfunc(putn(&lst_sat, datetime.));
39 %put &x;
18SEP21:00:00:00
40
41
42
43 PROC SQL;
44 CREATE TABLE TEMP.Cascade AS
2 The SAS System 15:13 Thursday, September 23, 2021

45 SELECT *
46 FROM DB.V_COLN_RSRC_PD_CALL_FACT_H_TST t1
47 where t1.CALENDAR_WK_ENDING_DATE = &x;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "X".
47 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.

48 Quit
49
50 %LET _CLIENTTASKLABEL=;
51 %LET _CLIENTPROCESSFLOWNAME=;
52 %LET _CLIENTPROJECTPATH=;
53 %LET _CLIENTPROJECTPATHHOST=;
54 %LET _CLIENTPROJECTNAME=;
55 %LET _SASPROGRAMFILE=;
56 %LET _SASPROGRAMFILEHOST=;
57
58 ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

58 ! *';*";*/;quit;run;
59 ODS _ALL_ CLOSE;
60
61
62 QUIT; RUN;
63

[ballardw · Accepted solution]

---

@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.

[PrudhviB]

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!