BookmarkSubscribeRSS Feed
SAS_user_n
Calcite | Level 5
Hello,

I am trying to query using PROC SQL and conditions on the dates (my date variable is DATE9. format) like this:

proc sql;
select ____ from
where date_var between '01MAY2008' and '01DEC2008'

but I am getting an error message like this: "The syntax of the string representation of a datetime value is incorrect"

And I also tried which did not work.

where date_var between '01MAY2008'd and '01DEC2008'd

and

where date_var between '01MAY2008'date9. and '01DEC2008'date9.

and the error message here is
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "d" was found following "between '01MAY2008'". Expected tokens may include: "". SQLSTATE=42601


Could anyone advise? Thank you!

Message was edited by: SAS_user_n Message was edited by: SAS_user_n
6 REPLIES 6
Doc_Duke
Rhodochrosite | Level 12
You should show the entire query that failed.

It looks like you may be using the SQL Pass-thru to DB2. If so, then you need to look at the SAS/Access documentation for DB2 to see how to pass a date (it's different, depending on the DB2 host).

If you converted your query to the LIBNAME style, the SAS translator should convert the 'ddmmmyyyy'd style to the appropriate internal style for DB2.
SAS_user_n
Calcite | Level 5
Thank you for your reply. Here is my entire query.
Could you shed some more light? Thank you.

==========================================
proc sql outobs=10;
connect to db2 (database=%sysget(DB2DBDFT));
create table temp as
select * from connection to db2
( select DATE_VAR_1
, VAR_2
, VAR_3

from XYZ.TABLE

where DATE_VAR_1 between '01MAY2008'd and '30NOV2008'd
) ;
run;

Message was edited by: SAS_user_n

Message was edited by: SAS_user_n Message was edited by: SAS_user_n
DBailey
Lapis Lazuli | Level 10
try this:

where date_var between '01May2008:0:0'dt and '01Dec2008:0:0'dt.

That's what I have to do to query oracle tables for date values.
SAS_user_n
Calcite | Level 5
Thanks for your reply. I tried your suggestion but since mine is db2, the suggestion did not work......
DBailey
Lapis Lazuli | Level 10
didn't realize you were making a connection in that manner.

I think the ISO default for DB2 is yyyy-mm-dd.
Doc_Duke
Rhodochrosite | Level 12
The WHERE clause is being shipped directly to DB2; SAS does not interpret it. You have to specify the date literals using the DB2 conventions. For that, you need a DB2 manual or guru. (I don't use DB2, but recognize those symptoms).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 3896 views
  • 0 likes
  • 3 in conversation