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