topic Re: Which transformation to use and how in SAS DI Studio in SAS Data Management

Which transformation to use and how in SAS DI Studio

umashankersaini — Sat, 12 Aug 2017 05:29:01 GMT

Hi Team,

I have a table which contains 11 variable (ID, Prod1--Prod10). I am getting desired output by following query but i need to make a DI job for the same, Kindly assist which transformation to use and how ( User written transformation is not allowed).

I tried with join + subqery but i was not able to make that, if this one is the correct way, it would be greatful if i could have step by step for that or any pdf link or video link.

Proc sql;
Select * from
(Select distinct(ID), Product1 from table1
Union
Select distinct(ID), Product1 from table1
Union
Select distinct(ID), Product2 from table1
Union
Select distinct(ID), Product3 from table1
Union
Select distinct(ID), Product4 from table1
Union
Select distinct(ID), Product5 from table1
Union
Select distinct(ID), Product6 from table1
Union
Select distinct(ID), Product7 from table1
Union
Select distinct(ID), Product8 from table1
Union
Select distinct(ID), Product9 from table1
Union
Select distinct(ID), Product10 from table1
)R;
quit;

Regards,

Uma Shanker Saini

Re: Which transformation to use and how in SAS DI Studio

LinusH — Sat, 12 Aug 2017 07:35:16 GMT

The obvious choice is the SQL Set Operators tranformation.
The documentation has examples that should get you going.

Re: Which transformation to use and how in SAS DI Studio

Patrick — Sun, 13 Aug 2017 10:45:33 GMT

@umashankersaini

Use the SQL SET transformation as @LinusH proposes.

I believe below SQL returns the same result and is a bit easier to define in DIS.

Proc sql;
  Select id, Product1 as Product from table1
    Union
  Select id, Product2 as Product from table1
    Union
  Select id, Product3 as Product from table1
  ;
quit;

Re: Which transformation to use and how in SAS DI Studio

umashankersaini — Mon, 21 Aug 2017 06:04:54 GMT

Hi Team,

Thanks for quick reply and sorry for my late response (implementation).

While using set operator transformation, i am not able to add same table for union ( as required).

I can add one table only one time but as per my need i have to do union on same table multiple time but i am unable to do so,

Kindly suggest.

Regards,

Uma Shanker Saini

Re: Which transformation to use and how in SAS DI Studio

Patrick — Mon, 21 Aug 2017 21:53:49 GMT

@umashankersaini

Drag the table metadata object multiple times onto the job canvas and link the objects all as input to the SQL SET transformation. This should allow you to use the same physical table multiple times.

Update

Just tried and actually what I've proposed above appears to not work. DIS doesn't let me connect the same metadata object more than once to the SQL SET transformation as input.

Below two approaches which work. The 2nd one using a Create Table transformation has the advantage that you can define the output table as a view and though you're not physically replicating the data multiple times.

Re: Which transformation to use and how in SAS DI Studio

umashankersaini — Tue, 22 Aug 2017 06:49:31 GMT

Thanks for your quick response.

I have used Extract transformation in place of create table or splitter and got the same result but i practiced with all.

Thank you so much for quick support.

Regards,

Uma Shanker Saini