Assigning ID code stored in External dataset file?

[SAS93]

I have a dataset I want to assign ID values based on what's stored in an "master" excel sheet I've been keeping for the relevant variables.

In this master list, variables are: year, Facility_name, facility_city, and the ID number I assigned myself. 

In the dataset I'm working with, I basically want SAS to read each row and, if it matches the year, name, and city, to assign the ID that's associated with it. 

SAS Dataset (have): 

Customer	Year	Facility_name	Facility_City
David	2018	Appleville Grocery	Smallville
Jane	2018	Appleville Grocery	Smallville
John	2018	Appleville Grocery	Large Town
Kelly	2017	Sunnyille Mall	Sunnyville

Master list, Excel file:

Facility_name	Facility_City	Year	ID
Appleville Grocery	Smallville	2018	1
Appleville Grocery	Large Town	2018	2
Sunnyville Mall - West	Sunnyvale	2018	3
Sunnyille Mall	Sunnyvale	2017	4

Final Dataset (want):

Customer	Year	Facility_name	Facility_City	ID
David	2018	Appleville Grocery	Smallville	1
Jane	2018	Appleville Grocery	Smallville	1
John	2018	Appleville Grocery	Large Town	2
Kelly	2017	Sunnyille Mall	Sunnyvale	4

[Reeza]

Bring your second data set into SAS via PROC IMPORT and "apply it" in a merge or a format. If the join is on multiple values such as facility name, city and year then I'd use a join. If it's on a single field I'd use PROC FORMAT.

[SAS93]

What kind of join would it be using Proc SQL? I'm uncertain exactly how to "apply" one dataset that's smaller to another that has repeated values, if that makes sense. 

[Reeza]

A left or right join depending on how you order the tables I suppose, if only one table has duplicates you need to merge on multiple variables or remove the duplicates ahead of time. I would assume you'd be merging by name/city/year which doesn't have duplicates.


Examples on how to merge are here
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lrcon&docsetTarget=n1tgk0...

Assuming both data sets have been sorted by name, city and year.





data want;
merge SASdatasetName (in=source1) ExcelFileName (in=source2);
by name city year;
if source1;

run;