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

DI Studio - Lookup Greater and Less Than

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

DI Studio - Lookup Greater and Less Than

Can I use the operators > and < in the Lookup transformation in DI Studio?

I need to find if a date is within a date range for a given key and to return only one record for each search.


Accepted Solutions
Solution
‎12-14-2013 11:16 PM
Respected Advisor
Posts: 3,887

Re: DI Studio - Lookup Greater and Less Than

To answer such questions it's often worth to look into the code DI Studio generates. The DI Lookup transformation generates a data step with a hash lookup - and a hash lookup works via exact key match.

The SQL Join transformation would allow for date ranges.

View solution in original post


All Replies
Solution
‎12-14-2013 11:16 PM
Respected Advisor
Posts: 3,887

Re: DI Studio - Lookup Greater and Less Than

To answer such questions it's often worth to look into the code DI Studio generates. The DI Lookup transformation generates a data step with a hash lookup - and a hash lookup works via exact key match.

The SQL Join transformation would allow for date ranges.

Contributor
Posts: 58

Re: DI Studio - Lookup Greater and Less Than

Tks Patrick!

I will then use the join.

The lookup always returns one record for each match found?

Super User
Posts: 5,256

Re: DI Studio - Lookup Greater and Less Than

Yes, the first one I believe if there are a multiple matches.

Data never sleeps
Contributor
Posts: 58

Re: DI Studio - Lookup Greater and Less Than

If I want to return only one record for each code below, which component should I use?

- Group by CD and rank by MAX(DATA)

Input:

cd dsc date

1  aaa  2013-01-31

1  bbb  2013-01-02

1 cccc  2013-01-21

2 xxxx  2013-01-12

2  yyyy  2013-01-15

Output:

cd dsc date

1  aaa  2013-01-31

2  yyyy  2013-01-15

Respected Advisor
Posts: 3,887

Re: DI Studio - Lookup Greater and Less Than

Untested - but with a SQL you would need something as below.

You could also use a Proc Sort and then a data step with "if.first cd" - but the data step would need to be user written code.

proc sql;

     create table want as

          select o.*

          from

               have o,

               (select cd, max(date) as max_date from have group by cd having max(date) as date) i

          where o.cd=i.cd and o.date=i.max_date

     ;

quit;

Contributor
Posts: 33

Re: DI Studio - Lookup Greater and Less Than

Try this using the join node with just one input

proc sql;

     create table want as

          select o.*

          from

               have o,

          having date=max(date)

     ;

quit;

Respected Advisor
Posts: 3,887

Re: DI Studio - Lookup Greater and Less Than

The hash code generated doesn't use "multidata" so for multiple keys only the first one will be loaded into the hash - and that's then what you get via a look-up.

Contributor
Posts: 58

Re: DI Studio - Lookup Greater and Less Than

The rank transformation does not automatically calculate this?

Input:

cd dsc date

1  aaa  2013-01-31

1  bbb  2013-01-02

1 cccc  2013-01-21

2 xxxx  2013-01-12

2  yyyy  2013-01-15

Output:

cd dsc date

1  aaa  2013-01-31

2  yyyy  2013-01-15

Respected Advisor
Posts: 3,887

Re: DI Studio - Lookup Greater and Less Than

May be it does. Make sure you don't have ties.

and.. you should always start a new thread for a new question and mark an answered thread as answered. Else everything becomes a mix-up like here.

Contributor
Posts: 58

Re: DI Studio - Lookup Greater and Less Than

OK. I will start a new thread. Tks.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 806 views
  • 0 likes
  • 4 in conversation