date time conversion to date9. in where clause

[CJM8]

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!!

[Astounding · Accepted solution]

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]

---

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

[CJM8]

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]

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.

[Kurt_Bremser]

See Maxim 28.

[Astounding]

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]

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]

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]

---

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

[Peter_C]

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]

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.