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