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)?
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.
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:
Then add the function information to the LIBNAME statement, so that the function is recognized for push down.
See this entry for more details:
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.