BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Abraham
Obsidian | Level 7

Hello Everyone

 

I want to import around 20 tables from Oracle to SAS. I am using pass through facility like below. But it tooks a lot of time due to huge number of records.

is there any alternative to follow in order to save time.

proc sql;
connect to oracle as ext(user=user orapw=pwd path=abcd);
CREATE TABLE emp as 
select * from connection to ext
(select e_id, ename from polaris. emp);
CREATE TABLE utub as 
select * from connection to ext
(select r_id, s_id from polaris. utubulu);  

create table table3------table 20  like above
disconnect from ext;
quit; 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If there are a huge nunber of records it is going to take time to process it, there is a physical relationship to number of records and processing time?  One way which may be quicker, although I doubt by very much is to goto the Oracle database - i.e. Log in to Toad or SQL Developer, and run the query there, export the result to a CSV file which you can then read into SAS.  

 

Note you seem to have typos in your code:

(select e_id, ename from polaris. emp);

Shouldn't be a space after polaris.

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If there are a huge nunber of records it is going to take time to process it, there is a physical relationship to number of records and processing time?  One way which may be quicker, although I doubt by very much is to goto the Oracle database - i.e. Log in to Toad or SQL Developer, and run the query there, export the result to a CSV file which you can then read into SAS.  

 

Note you seem to have typos in your code:

(select e_id, ename from polaris. emp);

Shouldn't be a space after polaris.

LinusH
Tourmaline | Level 20

Can't see the point of using explicit SQL pass-thru here, since your SQL is not doing any Oracle specific.

Use implicit SQL pass-theu instead, toghether with

options sastrace=',,,d' sastraceloc=saslog;

Which probably gives you some kind of timings in Oracle (so you can rule out network bottlenecks).

If Oracle is slow on pushing data, nothing much to about - talk to the DBAs.

 

What is the bigger picture here - you seem to copy whole datasets?

Is this a one time job?

If recurrent, perhaps you only need to load the delta.

Or let that data reside in Oracle - if you program wise in SAS, that could work perfectly fine.

Data never sleeps
Patrick
Opal | Level 21

If you really MUST download all the data from Oracle then one thing which could eventually speed up things is storing the data in SAS using the libname SPDE engine.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

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.

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
  • 3 replies
  • 1820 views
  • 0 likes
  • 4 in conversation