topic PROC SQL vs PL/SQL in SAS Programming

PROC SQL vs PL/SQL

Cherry — Thu, 30 Jun 2011 06:35:53 GMT

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?

PROC SQL vs PL/SQL

DF — Thu, 30 Jun 2011 08:27:55 GMT

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

FROM

DUAL

);

disconnect from oracle;

quit;

PROC SQL vs PL/SQL

SASJedi — Thu, 30 Jun 2011 16:49:42 GMT

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;