topic Re: Using a lookup table conditionally in SAS Programming

Using a lookup table conditionally

Walternate — Wed, 26 Aug 2015 15:35:57 GMT

Hi,

I have two datasets, one of which is my main dataset and the other of which is essentially a lookup table. The main dataset is at the person/date level and has ID, date, the key variable that I want to connect to the lookup table, and a categorical variable. The lookup table has a variable matching the key variable (though it has a different name) and the variable that I want to pull in to give me information about the key variable.

Main Dataset

ID Date Key_var Categ_var

1 4/5/15 123 A

1 3/9/14 456 B

1 2/3/15 123

2 2/1/13 B

2 1/5/12 789 A

Lookup Table:

Lookup_key_var Oth_var

123 abc

456 def

789 ghi

The issue is that while I want to keep all of the rows in the main dataset, I only want to join to the lookup table those rows that have a value of A for the categorical variable (other rows have other values, including missing, for this variable).

Any help is much appreciated.

Re: Using a lookup table conditionally

Reeza — Wed, 26 Aug 2015 15:41:36 GMT

Use a format with conditional assignment

if cat ne 'A' then other_var=put(lookup, $char_fmt.);

Re: Using a lookup table conditionally

ballardw — Wed, 26 Aug 2015 15:43:38 GMT

Please show what you expect the output to be. There are a couple of different solutions depending on what that output looks like.

Re: Using a lookup table conditionally

Jim_G — Wed, 26 Aug 2015 16:42:25 GMT

If the second data set is a reasonable size and stable build a table lookup with Proc Format and use the statement that Reeza suggested.

Proc format; value $char_fmt

‘123’=’abc’ ‘456’=’def’ ‘789’=’ghi’;

if cat_var =’A’ then oth_var=put(key_var,$char_fmt.); else oth_var=’ ‘;

Re: Using a lookup table conditionally

Walternate — Wed, 26 Aug 2015 16:51:50 GMT

The output dataset would look like this:

Main Dataset

ID Date Key_var Categ_var

1 4/5/15 123 A

1 3/9/14 456 B

1 2/3/15 123

2 2/1/13 B

2 1/5/12 789 A

Lookup Table:

Lookup_key_var Oth_var

123 abc

456 def

789 ghi

Combined dataset:

ID Date Key_var Categ_var Oth_var

1 4/5/15 123 A abc

1 3/9/14 456 B

1 2/3/15 123

2 2/1/13 B

2 1/5/12 789 A ghi

In other words, exactly the same as the Main dataset except that for rows where Categ_var=A, the lookup table has provided values of Oth_var (linked to values of Key_var).

Re: Using a lookup table conditionally

LinusH — Wed, 26 Aug 2015 17:09:04 GMT

A left join would do the trick and IMHO the easiest to code.

Re: Using a lookup table conditionally

Reeza — Wed, 26 Aug 2015 17:11:36 GMT

You need a CASE statement in the Join I think, wouldn't you?

Re: Using a lookup table conditionally

ballardw — Wed, 26 Aug 2015 17:30:15 GMT

proc sql;

create table want as

select Main.*, lookup.oth_var

from main left join lookup on main.key_var = lookup.key_var;

quit;

Re: Using a lookup table conditionally

Reeza — Wed, 26 Aug 2015 17:35:11 GMT

How does that take into account when Categ_var=B?

Re: Using a lookup table conditionally

LinusH — Wed, 26 Aug 2015 17:46:41 GMT

proc sql;

create table want as

select Main.*, lookup.oth_var

from main left join lookup on main.key_var = lookup.key_var and categ_var = 'A';

quit;

Re: Using a lookup table conditionally

Reeza — Wed, 26 Aug 2015 17:51:12 GMT

Thanks

Re: Using a lookup table conditionally

LinusH — Wed, 26 Aug 2015 17:52:20 GMT

My pleasure

Re: Using a lookup table conditionally

MikeZdeb — Wed, 26 Aug 2015 19:42:09 GMT

Hi, a more prolix approach than the SQL idea (assumes variable in LOOKUP is named same as in MAIN, i.e. KEY_VAR)...

data main;

retain oth_var 'xxx';

declare hash h(dataset: "lookup");

h.defineKey('key_var');

h.defineData('oth_var');

h.definedone();

do until (last);

set main end=last;

if categ_var eq 'A' then h.find();

output;

call missing(oth_var);

end;

stop;

run;