BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

13 REPLIES 13
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

ballardw
Super User

Date literals look like '01JAN2012'd

Reeza
Super User

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

SASKiwi
PROC Star

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?

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

Connecting to a DB2 server

sassharp
Calcite | Level 5

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

try this paper content if not update here

sassharp
Calcite | Level 5

another good article about

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

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

the UNIX® Environment

SASKiwi
PROC Star

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.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

I got it.

sassharp
Calcite | Level 5

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.

BobD
Fluorite | Level 6

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.

Maheshvaran
Calcite | Level 5

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

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
  • 13 replies
  • 1750 views
  • 0 likes
  • 7 in conversation