SAS/ACCESS and DB2 Date Fields

[rmwachter]

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.

DATA TestFile;
SET DB2LIB.DB2FILE;
WHERE DB2DATE = xxxxxxxx
KEEP FIELD1;
RUN;

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?

thanks.

[Patrick]

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.

HTH
Patrick

[sbb]

Useful reference link below for OP.

Scott Barry
SBBWorks, Inc.


SAS/ACCESS for Relational Databases: Reference, Second Edition, SAS/ACCESS Interface to DB2 Under z/OS
http://support.sas.com/documentation/cdl/en/acreldb/63023/HTML/default/a002299542.htm

[rmwachter]

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.

[Peter_C]

to properly see how SAS translates your code before passing it to db2, use the sas sytem option SASTRACE. It needs some parameters. There a.re good examples in online help.

[rmwachter]

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.

[rmwachter]

Let me throw a wrinkle to this - what if I would like to perform date math against it? Lets say I would like to run something like: WHERE DB2DATE = '07NOV2009'd - 365;

At the moment, it connects fine with the DB2 database, but does not use the correct index. Has anyone had any luck with date math in their selection statement using a data step?

[Peter_C]

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.
SO:
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.

PeterC