This has been raised previously, marked as solved, but really wasn't.
The SAS DI join transformation has a union option. This should allow multiple selections and where clauses to produce a single result. I can find no way to do this.
If I want a list of distinct names from two tables the SQL it should produce would look like:
Select name from employee where dept = 'abc'
union
select name from prospect where city = 'denver'
It only allows one map, one select, and one where clause.
I don't really want to use the set operator as I then want this to be a sub-query in the join transformation, not separate.
How can I do this?
Thanks.
The SQL JOIN Transformation lets you define a UNION JOIN. This is NOT the same like a UNION set operation.
Please refer to the discussion here and the links provided in the answer marked as solution.
If you really want to go for a UNION JOIN configure your SQL Transformation in a way so it creates code as follows:
select distinct
coalesce(l.name,r.name) as name
from
employee l
union join
prospect r
where l.dept = 'abc' or r.city = 'denver'
Alternatively - and easier to setup and maintain imho - use the SQL Set transformation and create a VIEW as target. Then use this view in your SQL Transformation.
This way the SQL Set generated logic will only get executed as part of the code generated by your SQL Join transformation.
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.