BookmarkSubscribeRSS Feed
rmwachter
Calcite | Level 5
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.
7 REPLIES 7
Patrick
Opal | Level 21
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Rhodochrosite | Level 12
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
Calcite | Level 5
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
Calcite | Level 5
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
Rhodochrosite | Level 12
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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1560 views
  • 0 likes
  • 4 in conversation