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!!
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
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
@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.
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?
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.
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 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?
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.
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!
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.
Ready to level-up your skills? Choose your own adventure.