data -
33485392 ABC JEBB...... NORTHEAST BUSINESS BANKING 330000
33485392 ABC JWHA...... COMMERCIAL BKG - CARD SVCS - OPERATING 109269
10019801 PQR JTCB...... LEASING - GOVERNMENT 8218732
04660104 XYZ JTCA...... LEASING - GENERAL INDUSTRIES 93723759
33485392 ABC JTCD...... LEASING - BUSINESS BANKING 18458
10019801 PQR JTCE...... ENERGY MANAGEMENT SERVICES 721138
04660104 XYZ JTCB...... LEASING - GOVERNMENT 9219179
12345678 ENERGY MANAGEMENT SERVICES 71138
33485392 ABC 5SSS...... SAG-SM BUS/PREMIER SPECIAL ASSETS 36000
12345678 LEASING - GOVERNMENT 82732
10019801 PQR JTAA...... LEASING GLOBAL INVESTMENT BANKING 63695049
12345678 LEASING GLOBAL INVESTMENT BANKING 639
04660104 XYZ JEIG...... HIG - GOVT 91741007
Columns - customer customer_name hier hier_name outstanding
-Read data into SAS
Logic to build -
1) If customer has any hierarchies with a 5 prefix, then assign that hier for all records of that customer
2) Rest of the customers getting special treatment for assigning Hierarchies-
Special treatment hierarchies are:
JT - Leasing
JW - GPS Card Payments
EF - Card
C - Debt Capital Raising Administration
R - GMG
G - Equities
If preponderance(highest) of outstanding balance points towards any of these "special hierarchies", then the process
continues its search for the next highest outstanding balance not identified in the "special hierarchies" list.
If not other hierarchy exists, then assign hierarchy based on highest outstanding balance.
ex-
cust hier outstanding assigned_hier
case-1
101 abc... 100 xyz... comment - there are no special treatment hierarchy so we will check for preponderence
101 xyz... 300 xyz... and we come to know that xyz hier has HIGHEST outstanding bal so we have assigned
101 pqr... 109 xyz... xyz... to assigned_hier
case-2
101 abc... 100 pqr... comment - Here based on preponderence we got jtz... bcz it has highest outstanding bal
101 jtz... 300 pqr... but jtz... is in special treatment so we need to skip that and check for next
101 pqr... 109 pqr... in preponderence which is 109 (pqr...) so assigned_hier = pqr...
case-3
101 cpc... 100 jtz... comment - Here all hier (c,jt,jw) are from special treatment so we will assign based on preponderence
101 jtz... 300 jtz... which is jtz...
101 jwr... 109 jtz...
33485392 ABC JEBB...... NORTHEAST BUSINESS BANKING 330000
33485392 ABC JWHA...... COMMERCIAL BKG - CARD SVCS - OPERATING 109269
10019801 PQR JTCB...... LEASING - GOVERNMENT 8218732
04660104 XYZ JTCA...... LEASING - GENERAL INDUSTRIES 93723759
33485392 ABC JTCD...... LEASING - BUSINESS BANKING 18458
10019801 PQR JTCE...... ENERGY MANAGEMENT SERVICES 721138
04660104 XYZ JTCB...... LEASING - GOVERNMENT 9219179
12345678 ENERGY MANAGEMENT SERVICES 71138
33485392 ABC 5SSS...... SAG-SM BUS/PREMIER SPECIAL ASSETS 36000
12345678 LEASING - GOVERNMENT 82732
10019801 PQR JTAA...... LEASING GLOBAL INVESTMENT BANKING 63695049
12345678 LEASING GLOBAL INVESTMENT BANKING 639
04660104 XYZ JEIG...... HIG - GOVT 91741007
Columns - customer customer_name hier hier_name outstanding
-Read data into SAS
Logic to build -
1) If customer has any hierarchies with a 5 prefix, then assign that hier for all records of that customer
2) Rest of the customers getting special treatment for assigning Hierarchies-
Special treatment hierarchies are:
JT - Leasing
JW - GPS Card Payments
EF - Card
C - Debt Capital Raising Administration
R - GMG
G - Equities
If preponderance(highest) of outstanding balance points towards any of these "special hierarchies", then the process
continues its search for the next highest outstanding balance not identified in the "special hierarchies" list.
If not other hierarchy exists, then assign hierarchy based on highest outstanding balance.
ex-
cust hier outstanding assigned_hier
case-1
101 abc... 100 xyz... comment - there are no special treatment hierarchy so we will check for preponderence
101 xyz... 300 xyz... and we come to know that xyz hier has HIGHEST outstanding bal so we have assigned
101 pqr... 109 xyz... xyz... to assigned_hier
case-2
101 abc... 100 pqr... comment - Here based on preponderence we got jtz... bcz it has highest outstanding bal
101 jtz... 300 pqr... but jtz... is in special treatment so we need to skip that and check for next
101 pqr... 109 pqr... in preponderence which is 109 (pqr...) so assigned_hier = pqr...
case-3
101 cpc... 100 jtz... comment - Here all hier (c,jt,jw) are from special treatment so we will assign based on preponderence
101 jtz... 300 jtz... which is jtz...
101 jwr... 109 jtz...