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

Hello - 

 

I am using SAS EG 7.1.  I am trying to convert a date time field in my data set to date9. format and can't seem to crack it. My where clause has a condition like this:

 

AND t1.txn_reqst_dt BETWEEN &dateS and &dateE

 

The &dateS and &dateE variables have already been converted to date9. format when i declared my global variables, however, the txn_rqst_dt field is a date time stamp field that looks like this 04MAY2018:13:15:32.238000

 

This is the error i get:

 

76 AND datepart(t1.txn_reqst_dt) BETWEEN &dateS and &dateE
NOTE: Line generated by the macro variable "DATES".
76 01Jan2018
_______
22
76
ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, AND, ||.

ERROR 76-322: Syntax error, statement will be ignored.

 

Any suggestions?

 

Thank you!!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

This is not a date in SAS:

 

01Jan2018

 

It's just a bunch of characters.  If you want to refer to a date, you would need to use:

 

"01Jan2018"d

 

So in your program that would translate to:

 

BETWEEN "&dateS"d and "&dateE"d

View solution in original post

11 REPLIES 11
Astounding
PROC Star

This is not a date in SAS:

 

01Jan2018

 

It's just a bunch of characters.  If you want to refer to a date, you would need to use:

 

"01Jan2018"d

 

So in your program that would translate to:

 

BETWEEN "&dateS"d and "&dateE"d

PaigeMiller
Diamond | Level 26

@CJM8 wrote:

I am trying to convert a date time field in my data set to date9. format and can't seem to crack it. My where clause has a condition like this:

 

AND t1.txn_reqst_dt BETWEEN &dateS and &dateE

 

The &dateS and &dateE variables have already been converted to date9. format when i declared my global variables


Formatting your macro variables to have a date9. format (or any other date or datetime or time format) for use in a where clause, or for use in any comparison, is usually a mistake. Leave them as un-formatted SAS date (or datetime or time) values.

--
Paige Miller
CJM8
Calcite | Level 5

Thank you for your replies. I removed the global variables, since they weren't really necessary for this anyway...and replaced with a prompt. However, i still get the following error:

 

78 AND datepart(t1.txn_reqst_dt) BETWEEN '&dateS'd and '&dateE'd
ERROR: Invalid date/time/datetime constant '&dateS'd.
ERROR: Invalid date/time/datetime constant '&dateE'd.

 

 

Any other suggestions?

PaigeMiller
Diamond | Level 26

You need double quotes, not single quotes, as stated above by @Astounding 

 

Or just get rid of the formatting and get rid of the quotes.

--
Paige Miller
Astounding
PROC Star
You didn't use my suggestion. Change the single quotes to double quotes. Single quotes suppress all macro activity including resolution of macro variables.
CJM8
Calcite | Level 5

thank you everyone - this ran correctly! 

 

AND datepart(t1.txn_reqst_dt) BETWEEN "&dateS"d and "&dateE"d

 

I am going to make a copy of the 28 maxims and send to my team if you don't mind!

 

thanks again

Peter_C
Rhodochrosite | Level 12
Passing date strings like 01Jan2018 in macro variables can still be used to check a transaction date-time, and your test would also work faster/better without the datepart() function
e.g.
t1.txn_reqst_dt BETWEEN "&dateS:0:0:0"dt and "&dateE:23:59:59.99"dt


Remember to use double quotes around macro vars you need expanded
PaigeMiller
Diamond | Level 26

@Peter_C wrote:
your test would also work faster/better without the datepart() function
e.g.
t1.txn_reqst_dt BETWEEN "&dateS:0:0:0"dt and "&dateE:23:59:59.99"dt




 

Wouldn't it work even faster/better without converting date/time values to formatted versions, and then having to "unformat" the date/time values in the WHERE clause via "&dateS:0:0:0"dt?

--
Paige Miller
Peter_C
Rhodochrosite | Level 12
Compile time of vanishingly small order can be compared with runtime execution of the datepart() function


It really is a vanishingly small amount of time to compile a date or datetime constant. 


Reading SAS logs to decypher a problem is far easier to validate when a macro var resolves to a date string rather than a number of days or seconds.

That is why I always recommend using a date string and date constants, rather than a "number of days"   to represent a date in coding 

(Use a very different approach for data)

ScottBass
Rhodochrosite | Level 12

I agree with @Peter_C .  I prefer using date and datetime literals, as the log messages are clearer.  The one-off compile time conversion of the date or datetime literal to the equivalent internal value would be minuscule.

 

 

data have;
   do dt="01JAN2018:00:00:00"dt to "31DEC2018:23:59:59"dt;
      output;
   end;
   format dt datetime.;
run;

data _null_;
   start="01APR2018:00:00:00"dt;
   end="30APR2018:23:59:59"dt;
   call symputx("start1",start);
   call symputx("start2",put(start,datetime.));
   call symputx("end1",  end);
   call symputx("end2",  put(end,datetime.));
run;
%put &=start1 &=start2;
%put &=end1 &end2;

data want1;
   set have;
   where dt between &start1 and &end1;
run;

data want2;
   set have;
   where dt between "&start2"dt and "&end2"dt;
run;

I like the log messages in want2 better.

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 4571 views
  • 2 likes
  • 6 in conversation