topic Re: date time conversion to date9. in where clause in SAS Programming

date time conversion to date9. in where clause

CJM8 — Thu, 28 Feb 2019 18:44:13 GMT

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

Re: date time conversion to date9. in where clause

Astounding — Thu, 28 Feb 2019 19:15:02 GMT

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

Re: date time conversion to date9. in where clause

PaigeMiller — Thu, 28 Feb 2019 19:27:54 GMT

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

Re: date time conversion to date9. in where clause

CJM8 — Thu, 28 Feb 2019 19:38:55 GMT

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?

Re: date time conversion to date9. in where clause

Kurt_Bremser — Thu, 28 Feb 2019 19:39:19 GMT

See Maxim 28.

Re: date time conversion to date9. in where clause

PaigeMiller — Thu, 28 Feb 2019 19:46:34 GMT

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.

Re: date time conversion to date9. in where clause

Astounding — Thu, 28 Feb 2019 19:48:09 GMT

You didn't use my suggestion. Change the single quotes to double quotes. Single quotes suppress all macro activity including resolution of macro variables.

Re: date time conversion to date9. in where clause

CJM8 — Thu, 28 Feb 2019 19:54:02 GMT

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

Re: date time conversion to date9. in where clause

Peter_C — Thu, 28 Feb 2019 19:55:22 GMT

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

Re: date time conversion to date9. in where clause

PaigeMiller — Thu, 28 Feb 2019 21:00:07 GMT

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

Re: date time conversion to date9. in where clause

Peter_C — Fri, 01 Mar 2019 06:07:15 GMT

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)

Re: date time conversion to date9. in where clause

ScottBass — Fri, 01 Mar 2019 07:16:33 GMT

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.