topic Re: How do I use a lookup table conditionally? in SAS Programming

How do I use a lookup table conditionally?

SSA — Wed, 10 Aug 2016 07:50:08 GMT

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

Re: How do I use a lookup table conditionally?

RahulG — Wed, 10 Aug 2016 07:53:41 GMT

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

Am I missing something in your question?

Re: How do I use a lookup table conditionally?

SSA — Wed, 10 Aug 2016 07:57:08 GMT

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.

Re: How do I use a lookup table conditionally?

RahulG — Wed, 10 Aug 2016 08:02:33 GMT

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;

Re: How do I use a lookup table conditionally?

SASKiwi — Wed, 10 Aug 2016 08:08:31 GMT

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;

Re: How do I use a lookup table conditionally?

SSA — Wed, 10 Aug 2016 08:06:25 GMT

Thanks! I'll try that out.

Re: How do I use a lookup table conditionally?

Kurt_Bremser — Wed, 10 Aug 2016 08:35:38 GMT

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.

Re: How do I use a lookup table conditionally?

LinusH — Wed, 10 Aug 2016 09:11:38 GMT

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.

Re: How do I use a lookup table conditionally?

SSA — Wed, 10 Aug 2016 22:26:56 GMT

Thanks very much, SASKiwi! Worked like a charm.

Re: How do I use a lookup table conditionally?

SSA — Wed, 10 Aug 2016 22:28:11 GMT

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).

Re: How do I use a lookup table conditionally?

SSA — Wed, 10 Aug 2016 22:30:04 GMT

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