I have some PROC SQL code that has to be terminated due to excessive time(>900 min) when run via PC SAS, but when I run the SQL code in PL/SQL it takes less than five minutes to run. Any suggestion on this?
I occassionally have this problem querying Oracle too. Have you tried running the code in pass-through mode?
SAS seems to do pretty awful things with SQL sometimes. Pass-thru will essentially have Oracle run your code and do all of the work, meaning it'll also take advantage of indexing and other Oracle optimizations.
proc sql;
connect to oracle (user=user pw=pass path='DBNAME');
create table MyTable as
select *
from connection to oracle
(
SELECT
1
FROM
DUAL
);
disconnect from oracle;
quit;
To get some visibility into what's going on, try the SASTRACE option. Using this option puts extra info in your log about what portions (if any) of the query are passed to the DB and what was processed in SAS.
Here's a generic program template for you to experiment with:
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname MyOra oracle user=UserID password=MyPW
schema=SchemaName;
proc sql;
select *
from MyOra.some_table_name
where Date < '26JUN1954'd
;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.