How do I use a lookup table conditionally?

[SSA]

Hi,

I've got a primary dataset with variables that I'd like to fill from a lookup table:

Primary Dataset

Seller  Buyer  Product  

City1   City2  X

City1   City2  Y

City1   City3  Z

City2   City1  A

City2   City3  A

City3   City1  W

Lookup Table

City   CityCode   Language   

City1  CAN         EN

City2  BLG         FR

City3  ELS          EN

Desired Table

Seller  Buyer  Product  Seller_CityCode  Seller_Language

City1   City2  X             CAN                     EN

City1   City2  Y             CAN                     EN

City1   City3  Z             CAN                     EN

City2   City1  A             BLG                     FR

City2   City3  A             BLG                     FR

City3   City1  W            ELS                     EN

Any assistance would be most appreciated.

SSA

[RahulG]

Proc sql left join should solve your problem.  Lookup table should be left joined to primary dataset.

Am I missing something in your question?

[SSA]

Thanks -- I'm a very new user, and haven't really used proc sql before.  Are you able to show me a sample code using the sample data I put on my post?

Thanks very much.

[SASKiwi · Accepted solution]

Try this:

Proc sql;
  Create table Desired as
  Select A.*
        ,B.CityCode
        ,B.Language
  From Primary as A
  Left join Lookup as B
  On A.Seller = B.City
  ;
Quit;

[SSA]

Thanks very much, SASKiwi! Worked like a charm.

[LinusH]

If you don't master programming/SQL queries, try to your hands on SAS Enterprise Guide or SAS Studio web client, where you have tasks (wizards) that guide you through standard operations like joins.

If you want to learn take the free online SAS programming training, and build up your competence from there.

[RahulG]

proc sql;
create table DESIRED_TABLE as
select 
t1.*
,t2.CityCode as Seller_CityCode
,t2.Language as  Seller_Language
from PRIMARY_DATASET t1 left jon
Lookup_Table t2 
on t1.Seller = t2.City;
quit;

[SSA]

Thanks!  I'll try that out.

[SSA]

Thanks very much for the speedy assistance, RahulG. I tried this, and it worked very well -- processing time was quick too (I had over 2 million records to process).

[Kurt_Bremser]

One of the nicest ways to deal with lookups is to create value formats from your lookup tables and use those. No joining or merging necessary.

The only limitation is that your lookup needs to fit into memory.

[SSA]

I checked this out, and it certainly looks like a good alternative to the proc sql option. Thanks!