Do the records in the "mapping" table have an order of precedence?
Assuming that they are ordered from least specific to most specific then something like this might work.
First convert your printouts into datasets.
I will add a ROW variable to data table to have a unique identifier.
I will add an LROW variable to the mapping table as a unique identifier and also to use to find the "best" match.
data have ;
infile cards dsd dlm='|' truncover;
length row BU 8 Currency $5 Prod_No Account_Number PL4-PL6 PC5-PC6 $40 ;
input BU Currency PL4-PL6 PC5-PC6 Prod_no Account_Number;
row+1;
cards;
8|NGN|Treasury Markets|Treasury Mkts - Inp|Treasury Mkts - Inp|Other Banking|BS Treasury Bills|738|115120
8|NGN|Treasury Markets|Treasury Mkts - Inp|Treasury Mkts - Inp|Other Banking|BS Due from Other Banks|761|122001
;
data lookup ;
infile cards dsd dlm='|' truncover;
length lrow 8 Mapping_Key $100 BSMap $40 Prod_No Account_Number PL4-PL6 PC5-PC6 $40 ;
input PL4-PL6 PC5-PC6 Prod_no Account_Number Mapping_Key BSMap;
lrow+1;
cards;
Treasury Markets| | | | | | |Treasury Markets|TMNostros
Treasury Markets|Treasury Mkts - Inp| | | |624| |Treasury MarketsTreasury Mkts - Inp624|TMLevel1Securities
Treasury Markets|Treasury Mkts - Inp| | | |738| |Treasury MarketsTreasury Mkts - Inp738|TMLevel1Securities
Treasury Markets|Treasury Mkts - Inp| | | |761X| |Treasury MarketsTreasury Mkts - Inp761X|TMNostros
Treasury Markets|Treasury Mkts - Inp| | | | |122051|Treasury MarketsTreasury Mkts - Inp122051|TMNostros
Treasury Markets|Treasury Mkts - Inp| | |BS Due from Other Banks| | |Treasury MarketsTreasury Mkts - InpBS Due from Other Banks|TMNostros
Treasury Markets|Treasury Mkts - Inp| | |BS Inv Sec - Debt Sec NGrp| | |Treasury MarketsTreasury Mkts - InpBS Inv Sec - Debt Sec NGrp|TMLevel1Securities
Treasury Markets|Treasury Mkts - Inp| | |BS Treasury Bills| | |Treasury MarketsTreasury Mkts - InpBS Treasury Bills|TMLevel1Securities
;
Now do a left join between the two and take the "best" result.
Here is a trick using COALESCE() to let you match the missing values in the mapping dataset.
proc sql ;
create table want as
select a.row,b.lrow,b.bsmap,b.mapping_key,* from have a
left join lookup b
on (a.pl4 = coalesce(b.pl4,a.pl4))
and (a.pl5 = coalesce(b.pl5,a.pl5))
and (a.pl6 = coalesce(b.pl6,a.pl6))
and (a.pc5 = coalesce(b.pc5,a.pc5))
and (a.pc6 = coalesce(b.pc6,a.pc6))
and (a.prod_no = coalesce(b.prod_no,a.prod_no))
and (a.account_number = coalesce(b.account_number,a.account_number))
group by a.row
having b.lrow = max(b.lrow)
;
quit;
Results:
Obs row lrow BSMap Mapping_Key
1 1 8 TMLevel1Securities Treasury MarketsTreasury Mkts - InpBS Treasury Bills
2 2 6 TMNostros Treasury MarketsTreasury Mkts - InpBS Due from Other Banks
Account_
Obs BU Currency Prod_No Number PL4 PL5 PL6
1 8 NGN 738 115120 Treasury Markets Treasury Mkts - Inp Treasury Mkts - Inp
2 8 NGN 761 122001 Treasury Markets Treasury Mkts - Inp Treasury Mkts - Inp
Obs PC5 PC6
1 Other Banking BS Treasury Bills
2 Other Banking BS Due from Other Banks
