Help using Base SAS procedures

SQL Date Issues

Reply
Regular Contributor
Regular Contributor
Posts: 238

SQL Date Issues

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

Regular Contributor
Regular Contributor
Posts: 238

SQL Date Issues

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.

Regular Contributor
Regular Contributor
Posts: 238

SQL Date Issues

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.

Super User
Posts: 11,343

SQL Date Issues

Date literals look like '01JAN2012'd

Super User
Posts: 19,878

SQL Date Issues

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

Super User
Posts: 3,261

SQL Date Issues

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?

Regular Contributor
Regular Contributor
Posts: 238

SQL Date Issues

Connecting to a DB2 server

Frequent Contributor
Posts: 110

SQL Date Issues

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

try this paper content if not update here

Frequent Contributor
Posts: 110

SQL Date Issues

another good article about

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

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

the UNIX® Environment

Super User
Posts: 3,261

SQL Date Issues

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.

Regular Contributor
Regular Contributor
Posts: 238

SQL Date Issues

I got it.

Frequent Contributor
Posts: 110

SQL Date Issues

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.

Contributor
Posts: 43

Re: SQL Date Issues

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.

Occasional Contributor
Posts: 13

SQL Date Issues

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

Ask a Question
Discussion stats
  • 13 replies
  • 797 views
  • 0 likes
  • 7 in conversation