topic Re: How does keep and rename data set options work with set statement with key = option. in SAS Programming

How does keep and rename data set options work with set statement with key = option.

john_floralde — Mon, 01 Aug 2022 08:49:30 GMT

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.

Re: How does keep and rename data set options work with set statement with key = option.

FreelanceReinh — Mon, 01 Aug 2022 12:47:58 GMT

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.

Re: How does keep and rename data set options work with set statement with key = option.

john_floralde — Tue, 02 Aug 2022 01:15:35 GMT

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.