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
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.
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;
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
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.
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.