SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Which transformation to use and how in SAS DI Studio

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 118
Accepted Solution

Which transformation to use and how in SAS DI Studio

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


Accepted Solutions
Solution
‎08-22-2017 02:46 AM
Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to umashankersaini

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

Capture.JPG

 

View solution in original post


All Replies
Super User
Posts: 5,424

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

Posted in reply to umashankersaini
The obvious choice is the SQL Set Operators tranformation.
The documentation has examples that should get you going.
Data never sleeps
Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to umashankersaini

@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;
Frequent Contributor
Posts: 118

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

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

Solution
‎08-22-2017 02:46 AM
Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to umashankersaini

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

Capture.JPG

 

Frequent Contributor
Posts: 118

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

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 225 views
  • 0 likes
  • 3 in conversation