03-06-2012 04:51 PM
I have this in my code
connect to odbc (dsn information to connect to table);
create table claims as select * from connect to odbc
inner join table2
on table1.member = table2.member
mkt_row_row_end_dt >= &date);
order by member, mktnm;
disconnect from odbc;
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
03-06-2012 04:52 PM
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.
03-06-2012 06:48 PM
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.
So the following code should work.
datepart(mkt_row_row_end_dt) >= &date
03-06-2012 08:28 PM
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?
03-08-2012 07:18 PM
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.
03-09-2012 03:36 PM
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!