Is it possible to reference a DB2 date field in a data step instead of a proc sql pass-thru method?
I've defined the DB2 library in my libname statement and can successfully select data from the data files using the pass-thru method, but when I attempt to access the same data using a data step, I am unable to find a way to make SAS recognize that the date value is a DB2 date value.
WHERE DB2DATE = xxxxxxxx
When I put the DB2DATE value in quotes it is not recognized ('2009-12-21').
When I put the DB2DATE valu in without quotes, it performs math operations against it = so 2009-12-21becomes ((2009-12)-21).
Is there an opcode or format that i can use or am i restricted to using the pass-thru method?
You can address DB2 from within a data step. SAS Access will then create a DB2 query to retrieve data from DB2, load this data into SAS and perform the data step commands which couldn't be translated into pass-through SQL code.
It's often better to write the selection within PROC SQL (pass-through or not) as SAS is then able to translate more into DB2 SQL which gets executed directly on the DB (you want to avoid to load millions of rows into SAS when you could write a SQL which selects only 100 rows to load into SAS).
For your question (not sure) try:
WHERE DB2DATE = '21DEC2009'd;
(you have to tell SAS that this string represents a DATE).
Not sure if DB2 needs a date ore datetime value '21DEC2009 0:0'dt.
Thanks for the help Patrick and SBB. My concern with using the date in the SAS format was the ability for the DB2 engine in translating it efficiently. So far though, testing has shown a very nice response time and the correct index is being accessed on the DB2 side.
Thanks Peter. I'm using the OPTION SASTRACE ',,,d' SASTRACE=SASLOG; at the moment with pretty good results.
My only complaint with it is that it doesn't show what index it's using when it access the DB2 data, only the primary file name. A minor inconvenience as all I have to do is hop over to my iSeries session and check the job connection to get the index being used.
I see no response to this, so let me guess:
SAS derives code to pass to db2 from the syntax you offer.
Where you offer a number SAS can make no assumptions about your expectations.
Where you offer a "date constant" you have provided guidance, and SAS should be able to convert that constant into a date constant in the standards of db2.
to achieve this arithmetic (date - 365 ), while continuing to indicate that the result is a date and not just a number, perform the calculation in the macro environment, returning the result as a date constant. That is just what the %SYSFUNC() seems designed to do ... [pre]WHERE DB2DATE = '07NOV2009'd - 365;[/pre]might be more effective if presented as[pre]WHERE DB2DATE = "%sysfunc( intnx( day, "07NOV2009"d, -365 ), date9 )"d ;[/pre]
hope this actually does what is wanted, but I'm not in a position to test it.