topic Re: SAS Dataset to Oracle table merge in SAS Programming

SAS Dataset to Oracle table merge

SASAna — Tue, 23 May 2017 17:21:58 GMT

Hi SAS Users,

Wanted some help in the tuning the below query, which is taking 2 hours to update the Oracle table after Oracle -SAS merge.

SAS datset has 1800 records. Oracle table is pretty big, but indexed on merging variables. after merging it is updating 4000 records.

libname test oracle user=&myid password=&mypwd path="&mydb" schema=test;

proc sql noprint;
update test.&Oracle_table as a
set
test_id =
(select test_id from sas_dataset as b
where a.&rec_id = b.&rec_id and a.run_id = b.run_id and a.run_date = b. run_date)
where exists
(select * from sas_dataset as b
where a.&rec_id = b.&rec_id and a.run_id = b.run_id and a.run_date = b. run_date)
;
quit;

libname test clear;

Thanks,

Ana

Re: SAS Dataset to Oracle table merge

kiranv_ — Tue, 23 May 2017 20:38:01 GMT

As SAS dataset is pretty small. Move the SAS dataset to Oracle and build index on the table and collect stats. Then do the update through explicit pass through.

Re: SAS Dataset to Oracle table merge

Sven111 — Tue, 23 May 2017 17:57:12 GMT

Given the small size of the SAS dataset, I'd recommend pushing it across to Oracle into a temporary table and doing the full update there locally using explicit SQL passthrough. I've had mixed results using implicit passthrough SQL with Oracle in the past, particularly when I'm trying to join/reference SAS datasets with Oracle tables like you're doing. I always try to push everything into either SAS or Oracle if I can help it. Plus, using explicit SQL passthrough will let you add optimizer hints if needed to make sure it's using the indexes properly (although there are some tricks to that too I can share if you're interested).

Re: SAS Dataset to Oracle table merge

SASAna — Tue, 23 May 2017 18:13:10 GMT

Yes please. I am interested to know that 🙂

Thanks,

Ana

Re: SAS Dataset to Oracle table merge

SASAna — Wed, 24 May 2017 02:55:47 GMT

Hi SAS users,

I tried this below as per your suggestion of adding option and creating the test table in ORacle DB. But i am getting error.

Can u please correct my mistake? I needed to create a empty table - so there is no "from".

Error - right paranthesis is missing.

code -

options dbidirectexec sastraceloc=saslog;

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute(
create table test.post_test as
select
(job_id char(10),
sale_id char(20),
reason char(2000),
run_id char(20),
run_date char(20)

)
);
disconnect from oracle;
quit;

Re: SAS Dataset to Oracle table merge

kiranv_ — Wed, 24 May 2017 03:39:47 GMT

You can oracle table through implicit pass through.

proc sql;

create table oratable.tablename as

select *

from sastable.tablename;

quit;

---Then create index

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute ( create index on tablename (columns) ) by oracle;
disconnect from oracle;
quit;

--then do the update with logic you have

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute ( your update logic goes here) by oracle;
disconnect from oracle;
quit;

Re: SAS Dataset to Oracle table merge

LinusH — Wed, 24 May 2017 06:07:59 GMT

There have quite a few similar questions on the communities if you care to search.
One hint though: DBKEY=.

Re: SAS Dataset to Oracle table merge

Sven111 — Wed, 24 May 2017 22:38:34 GMT

There were a couple of things that tripped me up on Oracle hinting from Explicit Pass-Through SQL. The first is that by default, SAS strips out any comments from the pass through, and since Oracle hints are passed as comments it won't send them by default. Depending on how you generally connect to Oracle from PROC SQL and what version of SAS you're on this can either be an easy fix or not. If your doing an explicit connection like this:

connect to oracle (user=myusr1 password=mypwd1);

then you can just add the paramater PRESERVE_COMMENTS:

connect to oracle as mycon(user=myusr1 password=mypwd1 preserve_comments);

But if you're using something older than SAS 9.2, even that won't work.

If you generally connect to Oracle via a LIBNAME statement (which is convenient since you can then use the same connection for both implicit and explicit pass-through SQL, there is no available option to enable you to preserve comments.

LIBNAME OracSAS     ORACLE     USER=&UserNm    PASS=&PassWd   PATH='SASDB'  CONNECTION=GLOBAL   SCHEMA=&UserNm;

PROC SQL;
        CONNECT USING OracSAS AS OracDB;
        ...SQL STUFF...
        DISCONNECT FROM OracDB;
;QUIT;

You can get around this problem (and the problem with old versions of SAS) by wrapping parts of the comments you want to use in %BQUOTE. I've added the following to my autoexec.sas file and then can just reference the macro vars as needed in my explicit pass-through code.

    %LET ORAC_HINT__APPEND      = %BQUOTE(/)%BQUOTE(*)+ APPEND %BQUOTE(*)%BQUOTE(/);
    %LET ORAC_HINT__PARALLEL_16 = %BQUOTE(/)%BQUOTE(*)+ PARALLEL (16) %BQUOTE(*)%BQUOTE(/);

I've also archived some papers I've found over the years that have been useful:

http://www.lexjansen.com/nesug/nesug05/io/io8.pdf

http://support.sas.com/resources/papers/proceedings13/072-2013.pdf

http://support.sas.com/resources/papers/proceedings13/081-2013.pdf

http://www2.sas.com/proceedings/sugi28/151-28.pdf

Hope that helps!

Re: SAS Dataset to Oracle table merge

SASAna — Fri, 26 May 2017 15:04:07 GMT

Thanks Kiran for the detailed steps. It worked very well and improved the processing time.

Re: SAS Dataset to Oracle table merge

SASAna — Fri, 26 May 2017 15:04:46 GMT

Thanks for the links and tips , it is really helpful 🙂