BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jwhite
Quartz | Level 8

Good Morning, All.

I've got a table with five ID columns that I need to lookup their value of on another table.

Say the columns are:

ID_1

ID_2

ID_3

ID_4

ID_5

I was thinking that I could use a loop transformation. First, set a control table with records 1-5, then iterate through a lookup job five times. The thing is, in that lookup job, how would I go about setting it up to find the value of ID_1 on the first iteration, then the value of ID_2 on the second, etc? As the lookup transformation requires mappings of columns.

Is there a way to do this? Am I on the right path or is there a better solution?

I'd rather not repeat the same lookup step five times, which is why I was hoping to just loop.

Thanks!

-Jeff

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

Without seeing what you have done, I think have all look-up in the same transformation is the most efficient. Each look-up tansformation will add another table scan of your source data. If your memory is being exhausted by a single look-up, is the only reason that I could see for going with another solution.

But, to see the hard facts for your situation, try them out and compare. Just try to avoid user written code if possible.

Data never sleeps

View solution in original post

5 REPLIES 5
DBailey
Lapis Lazuli | Level 10

without knowing more about the table layouts...  You could also create a view.  Depending on the size of the lookup table and if it was a sas dataset or an external dbms table, a hash lookup might be faster but comes with more complexity.

proc sql;

create table want as

select

b.id_1,

t1.lookup as id_1_lookup,

b.id_2,

t2.lookup as id_2_lookup,

b.id_3,

t3.lookup as id_3_lookup

b.id_4,

t4.lookup as id_4_lookup,

b.id_5,

t5.lookup as id_5_lookup

from

     base b

     left outer join lookup_table t1

          on b.id_1=t1.id

     left outer join lookup_table t2

          on b.id_2=t2.id

     left outer join lookup_table t3

          on b.id_3=t3.id

     left outer join lookup_table t4

          on b.id_4=t4.id

     left outer join lookup_table t5

          on b.id_5=t5.id

;

quit;

LinusH
Tourmaline | Level 20

DBailey, this is a DI Studio problem, not a SAS programming one.

JWhite, perhaps I don't see the whole picture, but building an iterations sounds unnecessary complicated, and ineffective. Not even sure if it could work without user written code.

You could do five look-up in the same transformation, just attach the look-up table five times.

The only (?) thing that could be a problem is that you will have the look-up tabled loaded in five copies in memory, so I hope it's not too large.

Data never sleeps
jwhite
Quartz | Level 8

Linus,

I ended up using five lookup transformations over user-written code, as it appears to be easier to read for any follow-up developers.

However, I just tried your trick of using five tables to one lookup transformation. That'll work as well.

Any idea which method is better/more efficient? The both have redundancies.

Thanks!

-Jeff

LinusH
Tourmaline | Level 20

Without seeing what you have done, I think have all look-up in the same transformation is the most efficient. Each look-up tansformation will add another table scan of your source data. If your memory is being exhausted by a single look-up, is the only reason that I could see for going with another solution.

But, to see the hard facts for your situation, try them out and compare. Just try to avoid user written code if possible.

Data never sleeps
Patrick
Opal | Level 21

If I understand your data correctly then your look-up table consists of simple key/value pairs. Your base table has 5 columns with keys for which you need to look-up the value always using the same look-up table with the same key/value pairs. If this is correct then doing this job with a single look-up transformations (and 5 look-ups) is more efficient than 5 passes through the base table with a single look-up in each pass (in the end you still need to read the look-up table 5 times).

From a perspective of performance: You could in a user written node (may be implemented as a user written transformation) create a SAS format out of your look-up table and then simply apply this format to your ID variables. This would only require one pass through the look-up table when creating the format.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2750 views
  • 0 likes
  • 4 in conversation