topic Re: proc sql and date format in SAS Procedures

proc sql and date format

SAS_user_n — Tue, 01 Jun 2010 17:16:26 GMT

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

Re: proc sql and date format

Doc_Duke — Tue, 01 Jun 2010 18:23:39 GMT

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.

Re: proc sql and date format

DBailey — Tue, 01 Jun 2010 18:26:54 GMT

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.

Re: proc sql and date format

SAS_user_n — Tue, 01 Jun 2010 18:48:01 GMT

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

Re: proc sql and date format

SAS_user_n — Tue, 01 Jun 2010 18:48:54 GMT

Thanks for your reply. I tried your suggestion but since mine is db2, the suggestion did not work......

Re: proc sql and date format

Doc_Duke — Tue, 01 Jun 2010 19:45:11 GMT

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

Re: proc sql and date format

DBailey — Tue, 01 Jun 2010 20:53:26 GMT

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

I think the ISO default for DB2 is yyyy-mm-dd.