topic DI Studio - UNION in SAS Data Management

DI Studio - UNION

spuckle — Fri, 05 Jan 2018 11:49:13 GMT

Hi,

I have a couple of problems I need some help with:

I've two tables with identical in columns that I want to UNION together - I'm using the SQL JOIN transformation, however I'm stumped on the Mappings tab. It appears you can only map one of the sources to the target, thus ignoring that it is a UNION.

I am also having issues with a CASE statement in the Target table - the CASE wizard allows you to select a column in the target table, however this errors when running. The error states an ambiguity as the same column name is in the two source tables even though I choose the target as the source.

I'm using DI Studio 4.9

TIA,

Gordon

Re: DI Studio - UNION join

RW9 — Fri, 05 Jan 2018 11:32:33 GMT

May I start by suggesting a look at SQL and what that means:

https://www.w3schools.com/sql/

As your use of UNION and JOIN makes no sense. They are two very different means of putting data together. Union is the process of putting data underneath another dataset, a join means to merge the data together by means of identifying variables.

With this: "The error states an ambiguity as the same column name is in the two source tables even though I choose the target as the source." - what this means is that the variable appears in both tables, and the compiler cannot decide which to take. I don't know DI and its UI, but in SQL you would assign an alias to each table and then utilse the alias to indicate to the compiler which to take:

select case when A.ID=1 then 1 else 2 end as RESULT
from MYTABLE A

The A after the dataset here is the alias.

Re: DI Studio - UNION

spuckle — Fri, 05 Jan 2018 11:58:47 GMT

I'm familiar with how to do it in SQL - in fact it's what I'm rebuilding in DI (for data lineage purposes).

UNION is under the SQL "Join" transformations in DI Studio

The SELECT node above is where columns are mapped, obviously in a UNION we have both sources populating a single column in the target table, which DI appears not to allow.

Re: DI Studio - UNION

RLigtenberg — Sat, 06 Jan 2018 00:20:40 GMT

The SQL Union is available in the "Set Operators" transformation which is in the "SQL" group on the Transformations tab.

Re: DI Studio - UNION

RLigtenberg — Mon, 08 Jan 2018 15:24:44 GMT

Here is some documentation.

The union in the join transformation performs a Union Join: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p1bk7i6jqseje7n1lifcip8kzhpp.htm

The union in the Set Operators transformation performs a “stacking” of tables and is probably what you are looking for: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0vo2lglyrnexwn14emi8m0jqvrj.htm