topic DI Studio: Where tab in Lookup Transformation question in SAS Data Management

DI Studio: Where tab in Lookup Transformation question

jwhite — Fri, 20 Feb 2015 15:04:00 GMT

DI Studio 4.9

So, I'm using a Lookup transformation, but am having some trouble building an expression in the where tab.

My source dataset has a key and a column with a sas_date. I use the key to look up a match on a second dataset. That works fine. However, the second dataset also has 'valid from date' and 'valid to date'. What I want to do is build an expression in the where tab where the sas_date from the first dataset falls within the date range of those two date fields in the second dataset.

Unfortunately, the expression builder in the where tab doesn't seem to recognize any columns or data from the first, source dataset but I seem to only be able to reference columns from the second dataset.

Am I using this transformation wrong? Something I'm missing? Do I need to incorporate some user written code?

Thanks!

Re: DI Studio: Where tab in Lookup Transformation question

LinusH — Fri, 20 Feb 2015 22:42:29 GMT

If you open the code tab you'll see that the lookup uses data step syntax. What you are describing is some kind of between - and - logic, wich is not possible here. You need to either use the Join transform or go with user written code.

Re: DI Studio: Where tab in Lookup Transformation question

jwhite — Mon, 23 Feb 2015 14:39:53 GMT

I see. So this kind of between and logic can't be used in a data step?

Thanks!

Re: DI Studio: Where tab in Lookup Transformation question

LinusH — Mon, 23 Feb 2015 15:11:49 GMT

Not without some serious tweaking. Data step uses BY processing as its primary technique, which means you can just name the join/merge variables.

And as a part of the Lookup transformation - definitely not possible.

So Join, or User written seems to be your options.

But I can see your need, been there myself. Since there are a lot of historization in DW/Data Mart dimensions, time should definitely be part of a standard lookup functionality.