Date math in Oracle using implicit passthrough

[sdennett]

I'm putting together some training materials for more novice SAS users in my company, specifically about how SAS passes SQL through to Oracle and how to improve query performance.  I'm having hard time finding a solution to a common problem and I'm hoping someone here can help.  To give you an idea of the level of experience of these users, most of them exclusively use Query Builder tasks in Enterprise Guide to build their queries, and all libraries are set up for them in EG through a metadata server (i.e., no one is writing out their own libname statements, or defining Oracle connections). 

Suppose I have a table in Oracle that has, among its many variable, two columns called timestamp_a and timestamp_b.  I want to get all the rows where timestamp_b is two hours (or some fixed constant) or more after timestamp_a.  I've tried several different where statements to accomplish this:

proc sql;
	create table want as
	select *
	from have
	where timestamp_b>timestamp_a+'02:00't;

	create table want as
	select *
	from have
	where timestamp_b-timestamp_a>'02:00't;

	create table want as
	select *
	from have
	where timestamp_b>timestamp_a+2*60*60;

	create table want as
	select *
	from have
	where timestamp_b-timestamp_a>2*60*60;

	create table want as
	select *
	from have
	where timestamp_b>intnx("dtday",timestamp_a,2,"S");
quit;

All ultimately do return valid results, but the problem none of them actually pass the where clause through to Oracle (verified by sastrace logs).  Without the where clause, Oracle passes the entire table to SAS to process which can be undesirable when the table is massive.  Does anyone know any good method for actually getting this type of simple date math to actually pass through to Oracle, with the caveat of not using either explicit passthrough or macros (my users probably aren't advanced enough for any techniques those areas)?

[s_lassen]

Not sure if you can. But it may be possible by using the SQL_functions=ALL option on your libname, hoping that the INTCK() function will be correctly translated:

libname oralib Oracle <options> SQL_functions=ALL;
Proc sql;
  create table want as select * from oralib.have
  where intck('SECOND',timestamp_a,timestamb_b)>2*60*60;
quit;

There may be a chance that SAS will pass the correct function to Oracle in order to calculate the number of seconds between the two datetimes. But check the results before giving this technique to your students; you may get unpredictable results.

[BrunoMueller]

Using Proc FedSQL you can do what you need. The IPTRACE option will show the SQL pushed down to the DBMS, code example courtesy to @SASJedi

proc fedsql iptrace;
  drop table fedsql_2_24 force;
  create table fedsql_2_24 as
  select
    *
  from
    xora.xtimetest
  where
    tsb > tsa + ( 2 / 24 )

  ;
quit;

However since your users are using SAS Enterprise Guide query window, I think you need to write two functions:

• One using Proc FCMP, so SAS will understand it
• One in Oracle doing the date math

Then add the function information to the LIBNAME statement, so that the function is recognized for push down.

See this entry for more details:

https://communities.sas.com/t5/Base-SAS-Programming/Using-a-Teradata-UDF-in-SAS-Implicit-Sql-Pass-Th...