topic Re: DI Studio - Lookup Greater and Less Than in SAS Data Management

DI Studio - Lookup Greater and Less Than

DavidCaliman — Sun, 15 Dec 2013 01:30:24 GMT

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.

Re: DI Studio - Lookup Greater and Less Than

Patrick — Sun, 15 Dec 2013 04:16:44 GMT

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.

Re: DI Studio - Lookup Greater and Less Than

DavidCaliman — Sun, 15 Dec 2013 12:31:25 GMT

Tks Patrick!

I will then use the join.

The lookup always returns one record for each match found?

Re: DI Studio - Lookup Greater and Less Than

LinusH — Sun, 15 Dec 2013 17:58:17 GMT

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

Re: DI Studio - Lookup Greater and Less Than

DavidCaliman — Sun, 15 Dec 2013 22:00:53 GMT

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

Re: DI Studio - Lookup Greater and Less Than

Patrick — Sun, 15 Dec 2013 22:33:17 GMT

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;

Re: DI Studio - Lookup Greater and Less Than

Patrick — Sun, 15 Dec 2013 22:35:32 GMT

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.

Re: DI Studio - Lookup Greater and Less Than

DavidCaliman — Mon, 16 Dec 2013 18:36:22 GMT

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

Re: DI Studio - Lookup Greater and Less Than

Patrick — Tue, 17 Dec 2013 01:44:42 GMT

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.

Re: DI Studio - Lookup Greater and Less Than

DavidCaliman — Tue, 17 Dec 2013 10:32:20 GMT

OK. I will start a new thread. Tks.

Re: DI Studio - Lookup Greater and Less Than

Barnipaz — Thu, 19 Dec 2013 16:40:18 GMT

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;