Copy datasets from Oracle to SAS

[prislo]

Hello!

I'am new in sas programming and i have some doubts about create datasets.

I'd like to know how is the best and faster way to copy databases from oracle to sas librarys.

I already do a simple code to create a database but I realized it was slow.

DATA WORK.t013_proposta_201802;
   SET ORA_LIB.T013_PROPOSTA;
   WHERE put(DATEPART(AT013_DTA_INCLUSAO), yymmn6.) = '201802';
RUN;

NOTE: The data set ABN_DS.T013_PROPOSTA_201802 has 22881 observations and 156 variables.
NOTE: DATA statement used (Total process time):
real time 15:38.93
cpu time 4:32.35

Is there a faster and more performative way to do this?

Thanks!

[RW9]

Not sure how we can help.  Is the connection to oracle running as fast as possible, where is SAS located, is the network speed good.  Is there a large amount of data, could you create a temporary table on the database with the filtered information so less is transmitted etc.

And please do not code all in uppercase, I don't want to be shouted at!

[andreas_lds]

The difference between real time and cpu time indicates that the sas-process is waiting for data. So talk to the oracle admins and network staff.

[kiranv_ · Accepted solution]

this may help and worth a try to use in database processing by using explicit pass through

I have pasted the code from http://support.sas.com/kb/41/616.html

datepart code should be very similar, change the where clause to oracle datepart from below code

and a quick google search will you help with datepart code

proc sql; connect to oracle(user=user password=password path=path);

create table work.job204 as

select * from connection to oracle

(select * from Employees where jobcode=204); quit;

[prislo]

Perfect!!

I made some changes and it worked correctly!

It worked very fast, in a few seconds.

Thanks so much!!