Help using Base SAS procedures

proc sql and date format

Reply
Occasional Contributor
Posts: 15

proc sql and date format

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
Trusted Advisor
Posts: 2,113

Re: proc sql and date format

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.
Occasional Contributor
Posts: 15

Re: proc sql and date format

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
Super Contributor
Posts: 578

Re: proc sql and date format

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.
Occasional Contributor
Posts: 15

Re: proc sql and date format

Thanks for your reply. I tried your suggestion but since mine is db2, the suggestion did not work......
Super Contributor
Posts: 578

Re: proc sql and date format

didn't realize you were making a connection in that manner.

I think the ISO default for DB2 is yyyy-mm-dd.
Trusted Advisor
Posts: 2,113

Re: proc sql and date format

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).
Ask a Question
Discussion stats
  • 6 replies
  • 1756 views
  • 0 likes
  • 3 in conversation