06-30-2011 02:35 AM
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?
06-30-2011 04:27 AM
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.
connect to oracle (user=user pw=pass path='DBNAME');
create table MyTable as
from connection to oracle
disconnect from oracle;
06-30-2011 12:49 PM
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
where Date < '26JUN1954'd