DATA Step, Macro, Functions and more

How do I use a lookup table conditionally?

Accepted Solution Solved
Reply
Occasional Contributor SSA
Occasional Contributor
Posts: 6
Accepted Solution

How do I use a lookup table conditionally?

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


Accepted Solutions
Solution
‎08-10-2016 06:26 PM
Super User
Posts: 3,254

Re: How do I use a lookup table conditionally?

[ Edited ]

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;

View solution in original post


All Replies
Super Contributor
Posts: 266

Re: How do I use a lookup table conditionally?

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

 

 

Am I missing something in your question?

Occasional Contributor SSA
Occasional Contributor
Posts: 6

Re: How do I use a lookup table conditionally?

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.

Solution
‎08-10-2016 06:26 PM
Super User
Posts: 3,254

Re: How do I use a lookup table conditionally?

[ Edited ]

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;
Occasional Contributor SSA
Occasional Contributor
Posts: 6

Re: How do I use a lookup table conditionally?

Thanks very much, SASKiwi! Worked like a charm.
Super User
Posts: 5,429

Re: How do I use a lookup table conditionally?

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.

Data never sleeps
Super Contributor
Posts: 266

Re: How do I use a lookup table conditionally?

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;
Occasional Contributor SSA
Occasional Contributor
Posts: 6

Re: How do I use a lookup table conditionally?

Thanks!  I'll try that out.

Occasional Contributor SSA
Occasional Contributor
Posts: 6

Re: How do I use a lookup table conditionally?

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).
Super User
Posts: 7,782

Re: How do I use a lookup table conditionally?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor SSA
Occasional Contributor
Posts: 6

Re: How do I use a lookup table conditionally?

Posted in reply to KurtBremser
I checked this out, and it certainly looks like a good alternative to the proc sql option. Thanks!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 448 views
  • 5 likes
  • 5 in conversation