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

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

10 REPLIES 10
RahulG
Barite | Level 11

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
Fluorite | Level 6 SSA
Fluorite | Level 6

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
PROC Star

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
Fluorite | Level 6 SSA
Fluorite | Level 6
Thanks very much, SASKiwi! Worked like a charm.
LinusH
Tourmaline | Level 20

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
RahulG
Barite | Level 11
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
Fluorite | Level 6 SSA
Fluorite | Level 6

Thanks!  I'll try that out.

SSA
Fluorite | Level 6 SSA
Fluorite | Level 6
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
Super User

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
Fluorite | Level 6 SSA
Fluorite | Level 6
I checked this out, and it certainly looks like a good alternative to the proc sql option. Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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