I am playing with pass-though option on SAS DI SQL transformations (SAS 9.4, SAS DI 4.9). My source and target objects are in different Oracle schemas and target schema can see source one's objects. I want to create a job to read data from source schema and put result into target schema. The problem is that as soon as I invoke pass-though automatically generated code uses source library to connect to database, but I need it to use target one. Is there any way I can do it without user-defined code?
I am creating a job in SAS DI using SQL SET transformation.
Source tables (Table 1 and Table 2) physical location is in Oracle library "Source_library" (using authdomain Source_Auth); Target_Table is located in Oracle library "Target_library" (using authdomain Target_Auth);
The generated code look like:
proc sql;
connect to ORACLE
(
PATH="<database>" AUTHDOMAIN=Source_Auth"
);
execute
(
insert into TARGET.TARGET_TABLE
/* Returns all unique rows from the two query results. */
select distinct
TABLE1.ID as ID
from
SOURCE.TABLE1 TABLE1
union
select distinct
TABLE2.ID as ID
from
SOURCE.TABLE2 TABLE2
) by ORACLE;
I need SAS DI to look like
proc sql;
connect to ORACLE
(
PATH="<database>" AUTHDOMAIN=Target_Auth"
);
I'm not sure if this would work, but try something like adding another input table as _INPUT1 which is from your target database, but don't use it in your query. I think DI will use _INPUT1 to determine the connection string.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.