@VVDR
With source and target both being in Oracle using explicit pass-through SQL is the right way to go which also gives you most flexibility.
When it comes to developing and tweaking such code then I use normally a client like SQL Developer or Dbiever.
I don't know anything about the data volumes nor your environment so can't say if the execution times are reasonable or not.
Running an explain for the code directly via a client (like SQL Developer) should tell you where you spend most time.
As a guess: You either don't have appropriate indexes defined and end up with full table scans or then the statistics are not updated.
To update the statistics below a piece of code "as is" which I've used in a past project. You will have to amend the code for your purposes.
/* update index stats on Ora target tables*/
%macro analize_index_compute_stat();
%local _source_tbl _ora_user;
%do i=0 %to %eval(&_output_count - 1);
%let _source_tbl=%upcase(&&_output&i);
%let _ora_user=;
proc sql noprint;
select sysvalue into :ora_user
from dictionary.libnames
where libname="%scan(&_source_tbl,1,.)"
;
quit;
proc sql;
connect to oracle as ora
( &ora_connection_string );
execute by ora
(
begin
for i in
(
select INDEX_NAME from USER_INDEXES
where table_name=%unquote(%nrbquote(')%scan(&_source_tbl,-1,.)%nrbquote('))
)
LOOP
execute immediate 'ANALYZE INDEX '||%nrbquote(')%trim(&ora_user).%nrbquote(')||i.INDEX_NAME||' COMPUTE STATISTICS';
end loop;
end;
);
disconnect from ora;
quit;
%end;
%mend;
%analize_index_compute_stat();
An additional option could be to use Oracle hints to have the Oracle SQL execute in parallel (you need to use Proc SQL option PRESERVE_COMMENTS for the SAS compiler not to remove such hints as comments).
....and as @SASKiwi already proposed: We are SAS experts. Why not ask an Oracle expert if you've got a problem with an Oracle only SQL.
