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
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;
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.
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;
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;
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).
Yes please. I am interested to know that 🙂
Thanks,
Ana
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.