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
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;
Proc sql left join should solve your problem. Lookup table should be left joined to primary dataset.
Am I missing something in your question?
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.
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;
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.
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;
Thanks! I'll try that out.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: