topic Re: Copy datasets from Oracle to SAS in New SAS User

Copy datasets from Oracle to SAS

prislo — Thu, 28 Feb 2019 14:37:16 GMT

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!

Re: Copy datasets from Oracle to SAS

RW9 — Thu, 28 Feb 2019 14:43:53 GMT

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!

Re: Copy datasets from Oracle to SAS

andreas_lds — Thu, 28 Feb 2019 15:12:38 GMT

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.

Re: Copy datasets from Oracle to SAS

kiranv_ — Thu, 28 Feb 2019 15:43:48 GMT

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;

Re: Copy datasets from Oracle to SAS

prislo — Thu, 28 Feb 2019 18:40:10 GMT

Perfect!!

I made some changes and it worked correctly!

It worked very fast, in a few seconds.

Thanks so much!!