topic Re: Assigning ID code stored in External dataset file? in SAS Programming

Assigning ID code stored in External dataset file?

SAS93 — Tue, 12 Jan 2021 14:58:06 GMT

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

Re: Assigning ID code stored in External dataset file?

Reeza — Tue, 12 Jan 2021 15:49:49 GMT

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.

Re: Assigning ID code stored in External dataset file?

SAS93 — Wed, 20 Jan 2021 17:57:00 GMT

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.

Re: Assigning ID code stored in External dataset file?

Reeza — Wed, 20 Jan 2021 19:05:22 GMT

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=n1tgk0uanvisvon1r26lc036k0w7.htm&locale=en#n1ctxgi9fb8kjhn1ni3dbuecjuno

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;