DATA Step, Macro, Functions and more

SAS Dataset to Oracle table merge

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

SAS Dataset to Oracle table merge

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


Accepted Solutions
Solution
‎05-26-2017 11:03 AM
PROC Star
Posts: 252

Re: SAS Dataset to Oracle table merge

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;

View solution in original post


All Replies
PROC Star
Posts: 252

Re: SAS Dataset to Oracle table merge

[ Edited ]

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.

 

 

 

Frequent Contributor
Posts: 84

Re: SAS Dataset to Oracle table merge

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;

Solution
‎05-26-2017 11:03 AM
PROC Star
Posts: 252

Re: SAS Dataset to Oracle table merge

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;

Frequent Contributor
Posts: 84

Re: SAS Dataset to Oracle table merge

Thanks Kiran for the detailed steps. It worked very well and improved the processing time.
Frequent Contributor
Posts: 82

Re: SAS Dataset to Oracle table merge

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).

Frequent Contributor
Posts: 84

Re: SAS Dataset to Oracle table merge

Yes please. I am interested to know that Smiley Happy

 

Thanks,

Ana

Frequent Contributor
Posts: 82

Re: SAS Dataset to Oracle table merge

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!

Frequent Contributor
Posts: 84

Re: SAS Dataset to Oracle table merge

Thanks for the links and tips , it is really helpful Smiley Happy
Super User
Posts: 5,256

Re: SAS Dataset to Oracle table merge

There have quite a few similar questions on the communities if you care to search.
One hint though: DBKEY=.
Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 240 views
  • 0 likes
  • 4 in conversation