It's a very straightforward merge if you reshape your lookup table appropriately.

Note that "KEY1" is not a key at all. Call it a pointer.

Instead of 1x80, the lookup dataset should be 20x7, with variables KEY2, KEY3, KEY4, A, D, O, and C.

Use PROC TRANSPOSE to change it from 1x80 to 80x1. Then use the SCAN function to crack the composite strings (A_1_0_19 and the like). Stick the first segment of each into a variable named _NAME_ and the other three into KEY2, KEY3, and KEY4. Sort and then use PROC TRANSPOSE a second time to get into the prescribed 20x7 shape.

Then sort the 500K dataset and merge.

On Fri, 4 Jun 2004 12:06:24 -0400, Danforth, Dennis <DDanforth@BBANDT.COM> wrote:

>Hi all, > >I have a "lookup" table that contains key translate values. There are four >possible keys, combining to 80 fields, each with a value. Key 3 and 4 are >always matched. > >KEY1 KEY2 KEY3_KEY4 >A 1 0_19 >D 2 20_39 >O 3 40_59 >C 4 60_79 > 80_99 > >The lookup table has 1 row and 80 columns: >HEADER: A_1_0_19 D_1_0_19 ... C_4_80_99 >VALUE: 200 322 999 > > >My other data set has 500,000 records and contains KEY 2, KEY 3, and KEY 4. > >HEADER: ID KEY2 KEY3 KEY4 >VALUE: MARK 1 0 19 > JOHN 4 80 99 > >For each record I want to look up and record the four possible values from >the key table. The resulting data set should look like this: >HEADER: ID KEY2 KEY3 KEY4 A D O C >VALUE: MARK 1 0 19 200 322 400 500 > JOHN 4 80 99 201 322 400 999 > > >My current line of thinking is to merge the datasets, use a SYMPUT function >to form the concatenated variable names, and then resolve the values. >Obviously this isn't working. The SYMGET function resolves my macro >variables as a text value, instead of as a variable name. So what does >anyone recommend? There has to be a better way... > >Thank to everyone, >Dennis D. >-------------------------------------------------------------------------- > >data key; > infile datalines dsd; > input a_1_0_19 d_1_0_19 a_4_80_99 d_4_80_99; >datalines; >20,222,999,322 >; >run; > >data values; > infile datalines dsd; > input key2 key3 key4; >datalines; >1,0,19 >1,0,19 >4,80,99 >; >run; > >data translated; > if _n_ = 1 then set key; > set values; > > call symput('a',( 'a_'|| > trim(left(put(key2,8.))) || > '_' || > trim(left(put(key3,8.))) || > '_' || > trim(left(put(key4,8.))) > )); > > call symput('d',( 'd_'|| > trim(left(put(key2,8.))) || > '_' || > trim(left(put(key3,8.))) || > '_' || > trim(left(put(key4,8.))) > )); > > A = symget('a'); > D = symget('d'); >run;