topic Re: DI Studio - union - How to product separate select statements in SAS Data Management

DI Studio - union - How to product separate select statements

duaneep1 — Wed, 29 Sep 2021 01:40:34 GMT

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.

Re: DI Studio - union - How to product separate select statements

Patrick — Tue, 05 Oct 2021 00:38:45 GMT

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.