topic SQL Date Issues in SAS Procedures

SQL Date Issues

tmm — Tue, 06 Mar 2012 21:51:00 GMT

I have this in my code

%let date='today2012'

proc sql;

connect to odbc (dsn information to connect to table);

create table claims as select * from connect to odbc

(select distinct

member,

memberage,

mktnm,

market_row_end_dt

from table1

inner join table2

on table1.member = table2.member

where

mkt_row_row_end_dt >= &date);

order by member, mktnm;

disconnect from odbc;

quit;

I am on SAS 9.2 and have tried all sorts of date things but nothing works. It says the syntax of the string representation of a datetime value is incorrect sqlstate=22007

SQL Date Issues

tmm — Tue, 06 Mar 2012 21:52:27 GMT

oops forgot there is ; after the 'today2012'

FYI this all runs fine without doing the mkt_row_end_dt ?=&date so I might have mistyped some of the things cuz I cannot copy and paste exact code in here. 1 reason it does not let you and another is I changed column names due to HIPAA and PHI.

SQL Date Issues

tmm — Tue, 06 Mar 2012 21:53:18 GMT

just need to know right way to formate that 1 piece that is all. Have had tons of input from other areas but nothing has worked.

SQL Date Issues

ballardw — Tue, 06 Mar 2012 22:22:04 GMT

Date literals look like '01JAN2012'd

SQL Date Issues

Reeza — Tue, 06 Mar 2012 23:48:05 GMT

I see two possible issues, you need to have a proper date literal format, so BallardW has pointed you towards that one. Secondly, servers tend to store datetime variables rather than just date variables in my experience.

%let date='01Jan2012'd;

So the following code should work.

datepart(mkt_row_row_end_dt) >= &date

SQL Date Issues

SASKiwi — Wed, 07 Mar 2012 01:28:37 GMT

You appear to be writing an SQL passthru query using an ODBC connection. This means the date string must conform to the standard required by the database you are connecting to, so it would be helpful to know what type of database you are connecting to: Oracle, SQL Server etc?

SQL Date Issues

tmm — Wed, 07 Mar 2012 14:02:43 GMT

Connecting to a DB2 server

SQL Date Issues

sassharp — Wed, 07 Mar 2012 17:20:56 GMT

http://www.nesug.org/Proceedings/nesug10/cc/cc35.pdf

try this paper content if not update here

SQL Date Issues

SASKiwi — Wed, 07 Mar 2012 19:46:22 GMT

This may also be helpful:

http://www.mathkb.com/Uwe/Forum.aspx/sas/26961/DB2-DATES-in-SAS

It suggests using 'yyyy-mm-dd' as your date constant.

SQL Date Issues

tmm — Thu, 08 Mar 2012 13:19:08 GMT

I got it.

SQL Date Issues

sassharp — Thu, 08 Mar 2012 17:10:48 GMT

Log into your account. Tell to community what is the fix or which post is useful answers. That one if some one has problem they follow those steps.

SQL Date Issues

sassharp — Thu, 08 Mar 2012 19:44:34 GMT

another good article about

Processing DB2/6000® Dates with SAS/ACCESS® in

http://support.sas.com/techsup/technote/ts566a.pdf

the UNIX® Environment

Re: SQL Date Issues

BobD — Fri, 09 Mar 2012 00:18:07 GMT

As previously said, since you are using SQL pass-thru, your macro variable should be defined as a valid date in DB2 syntax. The standard date syntax is YYYY-MM-DD, surrounded by single quotes. If you want today's date, you can use the function CURRENT_DATE instead.

In other words, if today is March 8, 2012, either of the following should work for you:

%let date = '2012-03-08';

%let date = current_date;

Hope this helps.

SQL Date Issues

Maheshvaran — Fri, 09 Mar 2012 20:36:29 GMT

Hi tmm,

Just off the cuff here, you may want to get the format in which the Date is stored in the Table and the when asking it in the Where clause use the followng syntax:

Note the Double qoutes and d.

WHERE mkt_row_row_end_dt >= "&date"d;

Hope this helps!

Kind Regards,

Maheshvaran