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
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.
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.
Date literals look like '01JAN2012'd
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
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?
Connecting to a DB2 server
http://www.nesug.org/Proceedings/nesug10/cc/cc35.pdf
try this paper content if not update here
another good article about
Processing DB2/6000® Dates with SAS/ACCESS® in
http://support.sas.com/techsup/technote/ts566a.pdf
the UNIX® Environment
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.
I got it.
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.