BookmarkSubscribeRSS Feed
Cherry
Obsidian | Level 7

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?

2 REPLIES 2
DF
Fluorite | Level 6 DF
Fluorite | Level 6

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;

SASJedi
SAS Super FREQ

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;

Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2950 views
  • 1 like
  • 3 in conversation