How make SAS DI SQL transformation to use alternative library for pass-through

AnnaMelnikova — Fri, 17 Nov 2017 02:56:24 GMT

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"
);

Re: How make SAS DI SQL transformation to use alternative library for pass-through

lethcons — Tue, 21 Nov 2017 09:09:37 GMT

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.

topic How make SAS DI SQL transformation to use alternative library for pass-through in SAS Data Management

How make SAS DI SQL transformation to use alternative library for pass-through

Re: How make SAS DI SQL transformation to use alternative library for pass-through