topic Re: Max Date in Extract Transformation in SAS Data Management

Max Date in Extract Transformation

Daylon_Hunt — Sat, 05 Mar 2016 00:12:52 GMT

In DI Studio, I'm working in an Extract Transformation and trying to insert a WHERE clause that will load only the most recent date into the Target table. The Numeric field is called "DWDATE", and the format is "yyyymmdd". I currently use the same logic for a table in SQL Management Studio, and the statement is:

FROM From.Dmi_Cif_Daily
WHERE DWDATE=(SELECT MAX(DWDATE)FROM DMI_CIF_Daily)

I'm trying to replicate this in the Extract Transformation in a job in DI Studio. Please help!

Re: Max Date in Extract Transformation

Reeza — Sat, 05 Mar 2016 01:38:22 GMT

Try:

where

dwdate=max(dwdate)

Re: Max Date in Extract Transformation

SASKiwi — Sat, 05 Mar 2016 02:46:02 GMT

I don't use DI Studio but it must be able to do an inner join like this:

select A.*
FROM Dmi_Cif_Daily as A
inner join
(SELECT MAX(DWDATE) as dwdate_max 
 FROM DMI_CIF_Daily
) as B
on A.DWDATE=B.dwdate_max

Re: Max Date in Extract Transformation

LinusH — Sat, 05 Mar 2016 07:19:27 GMT

First, if you wish to filter out records based on an aggregate function applied on the same table, use a having clause.
Second, <having> is not supported by the extract transformation, so you need to use the Join transformation instead.

Re: Max Date in Extract Transformation

LinusH — Sat, 05 Mar 2016 07:22:31 GMT

Addition : if you still like the sub query better, that is also supported in the Join transformation, perhaps not as intuitively as one could wish for, but it's there.

Re: Max Date in Extract Transformation

Daylon_Hunt — Mon, 07 Mar 2016 16:40:06 GMT

I'm a little confused as to how and why I would incorporate the Join Transformation if I'm not joining together multiple tables. I'm going from an Extract transformation to a Loader.

Re: Max Date in Extract Transformation

LinusH — Mon, 07 Mar 2016 16:50:43 GMT

DI Studio is a great tool, but not perfect. I think that the Join transformation was named to distinguish it from the extract. But in fact, it's a query builder. Joining is not required in the Join transformation.
Again, if you wish to use metadata, and have need for Having or sub query, Join is the transformation that does the job.

Re: Max Date in Extract Transformation

Daylon_Hunt — Thu, 10 Mar 2016 17:22:40 GMT

This definitely took some time for me to engineer my first time through, but in the end I was able to build the Having function into the Join transformation. Thank you LinusH!

Re: Max Date in Extract Transformation

smilbuta — Thu, 05 Oct 2017 15:58:56 GMT

Hello,

Daylon, can you share how you did this. I need to accomplish the same task in a New job we are creating and this is throwing me for a loop.