topic Re: Lookup rows in a different table based on column values in SAS Programming

Lookup rows in a different table based on column values

HSARAI — Fri, 28 Feb 2014 15:59:17 GMT

Hello there,

I've got a question I've yet to find an answer to. Let's say I have a table with columns containing a 1 or 0, something like this:

Value1	Value2	Value3	Value4
1	0	0	0
0	1	1	0
0	0	1	1
1	0	1	0
1	1	1	1

And another table that contains lookup values:

Value	Return
1	5
2	18
3	2
4	1

What I want to do is add a field which is the sum of the associated value in the lookup table if the indicator is a 1. So something like this:

Value1	Value2	Value3	Value4	Total
1	0	0	0	5
0	1	1	0	20
0	0	1	1	3
1	0	1	0	7
1	1	1	1	26

Thanks in advance

Re: Lookup rows in a different table based on column values

Reeza — Fri, 28 Feb 2014 16:04:20 GMT

Are your tables that small?

Re: Lookup rows in a different table based on column values

HSARAI — Fri, 28 Feb 2014 16:05:58 GMT

No. In reality there are 200+ indicator variables with close to a million rows.

Re: Lookup rows in a different table based on column values

Reeza — Fri, 28 Feb 2014 16:06:26 GMT

How big is the lookup table?

Re: Lookup rows in a different table based on column values

HSARAI — Fri, 28 Feb 2014 16:08:55 GMT

the lookup table has the same number of rows as the indicator variables, so 200+ rows.

Re: Lookup rows in a different table based on column values

art297 — Fri, 28 Feb 2014 16:33:14 GMT

Given your examples can you explain how you got the value of total for each row?

Re: Lookup rows in a different table based on column values

HSARAI — Fri, 28 Feb 2014 16:38:54 GMT

For the first row, just the value of Value1 is equal to 1. Therefore you look up the rows in the lookup table where Value=1, whose value of the Result column is 5.

For the second row, the value of Value2 and Value3 are equal to 1. Therefore you look up the rows in the lookup table where Value=2 and Value=3 and add the values of the Result column for those rows which are 18 and 2, sum is 20.

For the third row, the value of Value3 and Value4 are equal to 1. Therefore you look up the rows in the lookup table where Value=3 and Value=4 and add the values of the Result column for those rows which are 2 and 1, sum is 3.

And so on...

Re: Lookup rows in a different table based on column values

Reeza — Fri, 28 Feb 2014 16:41:21 GMT

You need to know the number of rows vars ahead of time, but you can easily put those into macro variables.

This solutions loads the lookup table into a temporary array and then filters through it. A Hash solution may even be more efficient.

data source;

input value1 value2 value3 value4;

cards;

1 0 0 0

0 1 1 0

0 0 1 1

1 0 1 0

1 1 1 1

;

run;

data lookup;

input value return;

cards;

1 5

2 18

3 2

4 1

;

run;

data want;

array lookup(4) _temporary_;

do i=1 to 4;

set lookup;

lookup(i)=return;

end;

do j=1 to 5;

array obs(4) value1-value4;

set source;

running_total=0;

do i=1 to 4;

running_total=running_total+obs(i)*lookup(i);

end;

output;

drop i j value return;

end;

run;

Re: Lookup rows in a different table based on column values

Tom — Fri, 28 Feb 2014 16:52:43 GMT

Why not just transpose the data ? So table one becomes:

RowId Value YesNo

1 1 1

1 2 0

1 3 0

1 4 0

1 5 0

2 1 0

2 2 1

....

Then you can just join with the lookup values an sum up RETURN to get TOTAL.

Re: Lookup rows in a different table based on column values

HSARAI — Fri, 28 Feb 2014 16:58:58 GMT

I considered a transpose solution too, but the problem is that the indicator variable isn't always the same name as the lookup table. So there could be some values where the column name is ValueABC and I would do a translation to look up the value where the Value is 'XYZ' in the lookup table. I should have specified this, but even though a transpose solution would work with the example I provided it wouldn't unfortunately work for the actual problem I am trying to solve.

Re: Lookup rows in a different table based on column values

data_null__ — Fri, 28 Feb 2014 17:05:47 GMT

You could consider PROC SCORE. You again need to process the look up table and name the "VALUE" to match the name in the master. But you don't have to know how many.

data source;
   input value1 value2 value3 value4;
   cards; 
1    0    0    0
0    1    1    0
0    0    1    1
1    0    1    0
1    1    1    1
;;;;
   run; 
 

data lookup;
   input value return;
   cards; 
1    5
2    18
3    2
4    1
;;;;
   run; 
proc transpose data=lookup prefix=value out=score;
   id value;
   var return;
   run; 
data score;
   retain _type_ 'SCORE' _name_ 'SUM'; 
   set score(drop=_name_);
   run; 
proc print; 
   run; 
proc score data=source score=score out=scored;
   run; 
proc print; 
   run; 

Re: Lookup rows in a different table based on column values

Reeza — Fri, 28 Feb 2014 17:08:02 GMT

This is also basic matrix math, so IML will probably handle it very easily.

Lots of options

Re: Lookup rows in a different table based on column values

HSARAI — Fri, 28 Feb 2014 17:12:01 GMT

I'm sure there are a lot of options since I can't imagine this being uncommon. I just need to try understand your proposed solution with my minimal experience of the SAS language, but I'm hoping I can let you know soon if I worked something out.

Re: Lookup rows in a different table based on column values

art297 — Fri, 28 Feb 2014 17:36:32 GMT

Below is a fairly simple way. The first line creates a macro variable called &n. and sets it to the number of rows that you have, thus you would just have to change it from 4 to 200 (or however number of values that you actually have). _n_ is an automatic counter that keeps track of the number of the row you are actually reading. However, within an interation _n_ can be reused and it won't forget its correct value when it gets to the next iteration.

data table1;

input Value1-Value4;

cards;

1 0 0 0

0 1 1 0

0 0 1 1

1 0 1 0

1 1 1 1

;

run;

data table2;

input Value Return;

cards;

1 5

2 18

3 2

4 1

;

run;

%let n=4;

data want (drop=Value Return);

array recodes(&n.)_temporary_;

array values(*) value1-value&n.;

if _n_ eq 1 then do;

do _n_=1 to &n.;

set table2;

recodes(value)=Return;

end;

set table1;

do _n_=1 to dim(values);

total=sum(total,values(_n_)*recodes(_n_));

end;

run;

Re: Lookup rows in a different table based on column values

Tom — Fri, 28 Feb 2014 19:01:38 GMT

You will need to get a solution to matching the 0/1 variables to the proper weight value from your lookup table.

The right answer probably depends on why the names are not matching now. If it is just because users are entering names manually perhaps you can use the order of the variables in the dataset to drive the match?

Call your first dataset HAVE and your value lookup table LOOKUP. Then this code should create new dataset WANT that is a copy of HAVE with the new TOTAL variable appended on the right.

data want ;

set have ;

array all _numeric_;

do _i_ = 1 to dim(all) ;

if all(_i_) then do;

set lookup (keep=return) point=_i_ ;

total = sum(total,return);

end;

drop return ;

run;

Re: Lookup rows in a different table based on column values

art297 — Fri, 28 Feb 2014 20:03:38 GMT

I just noticed your post about the variable name mismatch. The same logic I originally suggested could easily be adapted for non-matching names. e.g.:

data table1;

input ValueABC ValueQQQ ValueAAA ValueCCD;

cards;

1 0 0 0

0 1 1 0

0 0 1 1

1 0 1 0

1 1 1 1

;

run;

data table2;

input Value Return;

cards;

1 5

2 18

3 2

4 1

;

run;

%let n=4;

data want (drop=Value Return);

array recodes(&n.)_temporary_;

if _n_ eq 1 then do;

do _n_=1 to &n.;

set table2;

recodes(value)=Return;

end;

set table1;

array values(*) ValueABC--ValueCCD;

do _n_=1 to dim(values);

total=sum(total,values(_n_)*recodes(_n_));

end;

run;

Re: Lookup rows in a different table based on column values

Haikuo — Sat, 01 Mar 2014 00:10:35 GMT

Hash seems pretty handy for this kind of lookup:

data source;

input value1 value2 value3 value4;

cards;

1 0 0 0

0 1 1 0

0 0 1 1

1 0 1 0

1 1 1 1

;;;;

run;

data lookup;

input value return;

cards;

1 5

2 18

3 2

4 1

;;;;

data want;

if _n_=1 then do;

declare hash h(dataset:'lookup');

h.definekey('value');

h.definedata('return');

h.definedone();

call missing (value,return);

end;

set source;

array _v value1-value4;

do over _v;

if _v=1 then do;

rc=h.find(key:_i_);

total=sum(total,return);

end;

drop value return rc;

run;

Haikuo

Re: Lookup rows in a different table based on column values

HSARAI — Mon, 03 Mar 2014 15:34:10 GMT

There were many different options proposed but I feel that the hash solution is the most flexible, though perhaps not the most efficient. Thank you everyone for your help!

Re: Lookup rows in a different table based on column values

kaushal2040 — Mon, 03 Mar 2014 15:55:43 GMT

Hi Art,

I have seen using if _n_=1 then do in hash objects. However, what is the purporse of it in regular SAS programms other than hash programming. From what I understand, it means do something for the first iteration of data step. Does this means only do for the first observation of the data and what about other data step iterations? Thank you for your clarification.

Re: Lookup rows in a different table based on column values

HSARAI — Mon, 03 Mar 2014 15:58:34 GMT

kaushal2040, I can answer that since I encountered it myself. The purpose for using the if _n_=1 flag is so that the hash object is only defined once. If the flag is not used, then the hash object is re-declared for every row of the dataset, which is unnecessary.