SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio: Where tab in Lookup Transformation question

Reply
Frequent Contributor
Posts: 89

DI Studio: Where tab in Lookup Transformation question

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!

Super User
Posts: 5,432

Re: DI Studio: Where tab in Lookup Transformation question

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.

Data never sleeps
Frequent Contributor
Posts: 89

Re: DI Studio: Where tab in Lookup Transformation question

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

Thanks!

Super User
Posts: 5,432

Re: DI Studio: Where tab in Lookup Transformation question

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.

Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 660 views
  • 2 likes
  • 2 in conversation