BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PrudhviB
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Show us the entire macro and PROC SQL code.

 

Show us the entire LOG from running this code.

--
Paige Miller
PrudhviB
Obsidian | Level 7
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
Super User

@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
Obsidian | Level 7

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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 700 views
  • 1 like
  • 3 in conversation