SAS DI Studio Oracle Join Transformation execute as 'CTAS'

JeroenPotgieter — Thu, 14 Apr 2022 13:23:25 GMT

Hi all,

In DI Studio I want to join two oracle table with pass-through facility. In the standard execution 'join' tranformation, SAS DI pefroms the following steps :

1. It creates the table in Oracle

2. And with an insert it loads the data into the table.

All this with bad performance.

Is it possible with a standard 'join' transformation to let DI Studio make only a 'Create Table As' SQL? Or is an option available, that I'm not aware of, in SAS DI that will create a 'CTAS' sql query?

Current Situation:

data Oracle-libname.[tablename]

(dbnull = (
columnname1 = NO
columnname2 = NO
columnname3 = NO));
attrib columnname1 length = $32
format = $32.
informat = $32.
label = 'columnname1';
attrib columnname2 length = $32
format = $32.
informat = $32.
label = columnname2';
attrib columnname3 length = $32
format = $32.
informat = $32.
label = 'columnname3';
call missing(of _all_);
stop;
run;

proc sql;
connect to ORACLE
(
[Connection string]
);
execute
(
insert into oracle-location.oracle-table
select
A.columnname1,
A.columnname2,
A..columnname3
from
oracle-location.oracle-tablename1 A inner join
oracle-location.oracle-tablename2 B
on
(
A..columnname1 = B.columnname1
and B.columnname2 = 1
)

) by ORACLE;

%rcSet(&sqlrc);

disconnect from ORACLE;
quit;

Preferred Sitation:

proc sql;
connect to ORACLE
(
[Connection string]
);
execute
(
create table oracle-location.oracle-table as (
select
A.columnname1,
A.columnname2,
A..columnname3
from
oracle-location.oracle-tablename1 A inner join
oracle-location.oracle-tablename2 B
on
(
A..columnname1 = B.columnname1
and B.columnname2 = 1
))

) by ORACLE;

%rcSet(&sqlrc);

disconnect from ORACLE;
quit;

Thank you!

Re: SAS DI Studio Oracle Join Transformation execute as 'CTAS'

JeroenPotgieter — Mon, 18 Apr 2022 19:30:33 GMT

Anyone?? Any help would be appreciated..

Re: SAS DI Studio Oracle Join Transformation execute as 'CTAS'

Patrick — Tue, 19 Apr 2022 00:01:33 GMT

I don't believe OOT DIS transformations can generate your desired syntax.

You can always go for a user written code transformation or if you want something reusable for a custom transformation. And to keep things still to a certain degree metadata driven you then can use the DIS generated macro variables in your code.

If going for user written code: Make sure you design and write this code in a way so it's re-runnable (like: drop table if it already exists).

topic Re: SAS DI Studio Oracle Join Transformation execute as 'CTAS' in SAS Programming

SAS DI Studio Oracle Join Transformation execute as 'CTAS'

Re: SAS DI Studio Oracle Join Transformation execute as 'CTAS'

Re: SAS DI Studio Oracle Join Transformation execute as 'CTAS'