We have the following code where we perform a table lookup using set statement with key = option.
*master table;
data ftr;
trnid = 'trade1';
trnidsce = 'a';
issueid = '';
issueidsce = '';
rcvudlyid = 'ABC';
rcvudlyidsce = '2';
run;
*lookup table;
data ins (index = (issueKey = (issueid issueidsce) / unique)) ;
issueid = 'ABC';
issueidsce = '2';
ciultarlvcflg = 'Y';
run;
*final table;
data final;
set ftr;
set ins2 (keep = issueid issueidsce ciultarlvcflg
rename = (issueid = rcvudlyid
issueidsce = rcvudlyidsce)) key = issueKey / unique;
if _iorc_ then _error_ = 0;
run;
As I understand the key = option should do the lookup via definition of issueKey, i.e. if issueid and issueidsce is matching between ins2 and ftr dataset. However it seems the lookup is happening by rcvudlyid rcvudlyidsce after the rename and I am now able to get value of ciultarlvcflg. I was expecting it to throw an error given the original variables used to create the index was renamed. Can someone help me to understand what is happening? Many thanks.
Hello @john_floralde,
The KEEP= option is (always) applied before the RENAME= option.
If the key variables in dataset INS were renamed permanently with PROC DATASETS, the index would be modified correspondingly (see PROC CONTENTS output), thus maintaining its integrity. Apparently, SAS handles a temporary renaming via dataset options in a similar way: temporarily the index works with the new names. (I assume that the "2" in ins2 in your code was not intended.)
This is a useful feature because it allows you to create indexed lookup tables without worrying about variable names in future "master" tables. Your example is a case in point, as simply renaming the RCVU... key variables of dataset FTR is not an option because of the existing ISSUE... variables. So, one lookup table can be used for many master tables, regardless of their key variable names.
Hello @john_floralde,
The KEEP= option is (always) applied before the RENAME= option.
If the key variables in dataset INS were renamed permanently with PROC DATASETS, the index would be modified correspondingly (see PROC CONTENTS output), thus maintaining its integrity. Apparently, SAS handles a temporary renaming via dataset options in a similar way: temporarily the index works with the new names. (I assume that the "2" in ins2 in your code was not intended.)
This is a useful feature because it allows you to create indexed lookup tables without worrying about variable names in future "master" tables. Your example is a case in point, as simply renaming the RCVU... key variables of dataset FTR is not an option because of the existing ISSUE... variables. So, one lookup table can be used for many master tables, regardless of their key variable names.
Interesting. Thank you very much for your explanation! And yeah you are write I miswrote ins2 instead of ins when doing the lookup in the code.
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.