BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

5 REPLIES 5
LinusH
Tourmaline | Level 20
The obvious choice is the SQL Set Operators tranformation.
The documentation has examples that should get you going.
Data never sleeps
Patrick
Opal | Level 21

@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;
umashankersaini
Quartz | Level 8

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

Patrick
Opal | Level 21

@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

 

umashankersaini
Quartz | Level 8

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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