Cross Tabulate Data to Create FLAG on Mock Data

[pdata]

Hi Team,

I have TWO Datasets 

    1) GRADE

    2) ACCT

GRADE dataset is kind of a cross table. It tells the Severity -- between Grade values and Balance values

e.g: Grade=1 & BAL6=9000 -- then severity is VeryLow etc

data Grade;
input Grade $ : 15. BAL1 $ BAL2 $ BAL3 $ BAL4 $ BAL5 $ BAL6 $ BAL7 $ BAL8 $ BAL9 $ BAL10 $;
datalines;
0 2200 3500 5000 6250 7500 9000 10500 12500 15000 999999
1 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow
2 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow
3 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow Low Low Low Low
4 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow Low Low Low Low
5 VeryLow VeryLow VeryLow VeryLow Low Low Low Low Low Medium
6 VeryLow VeryLow VeryLow Low Low Low Low Low Medium Medium
7 VeryLow VeryLow Low Low Low Low Low Medium Medium Medium
8 VeryLow VeryLow Low Low Low Low Low Medium Medium Medium
9 VeryLow Low Low Low Low Low Medium Medium Medium High
10 VeryLow Low Low Low Low Medium Medium Medium Medium High
11 VeryLow Low Low Low Medium Medium Medium Medium High High
12 Low Low Low Medium Medium Medium Medium High High High
13 Low Low Medium Medium Medium Medium High High High High
14 Low Medium Medium Medium Medium High High High High High
15 Low Medium Medium Medium Medium High High High High High
16 Low Medium Medium Medium High High High High High High
17 Low Medium Medium High High High High High High High
18 Medium Medium Medium High High High High High High High
19 Medium Medium High High High High High High High High
20 Medium Medium High High High High High High High High
21 Medium High High High High High High High High High
22 Medium High High High High High High High High High
23 Medium High High High High High High High High High
24 Medium High High High High High High High High High
25 Medium High High High High High High High High High
;
run;

data Acct;  /*Mock Data */
input acctno Grade Balance;
datalines;
111 1 20000
222 10 14550
9887 18 4065
;
run;

Now the WANT dataset should have new column TAG-- holding the Severity values.

acctno  Grade Balance   FLAG
111       1          20000      VeryLow
222     10          14550      Medium
9887   18         4065         Medium
;
run;

Please advice.

Thank You

[Reeza · Accepted solution]

That first table is in a very bad structure for lookups. Restructuring it makes the lookup trivial.

data Grade;
informat GRADE 8. BAL1-BAL10 $10.;
input Grade  BAL1 $ BAL2 $ BAL3 $ BAL4 $ BAL5 $ BAL6 $ BAL7 $ BAL8 $ BAL9 $ BAL10 $;
datalines;
0 2200 3500 5000 6250 7500 9000 10500 12500 15000 999999
1 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow
2 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow
3 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow Low Low Low Low
4 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow Low Low Low Low
5 VeryLow VeryLow VeryLow VeryLow Low Low Low Low Low Medium
6 VeryLow VeryLow VeryLow Low Low Low Low Low Medium Medium
7 VeryLow VeryLow Low Low Low Low Low Medium Medium Medium
8 VeryLow VeryLow Low Low Low Low Low Medium Medium Medium
9 VeryLow Low Low Low Low Low Medium Medium Medium High
10 VeryLow Low Low Low Low Medium Medium Medium Medium High
11 VeryLow Low Low Low Medium Medium Medium Medium High High
12 Low Low Low Medium Medium Medium Medium High High High
13 Low Low Medium Medium Medium Medium High High High High
14 Low Medium Medium Medium Medium High High High High High
15 Low Medium Medium Medium Medium High High High High High
16 Low Medium Medium Medium High High High High High High
17 Low Medium Medium High High High High High High High
18 Medium Medium Medium High High High High High High High
19 Medium Medium High High High High High High High High
20 Medium Medium High High High High High High High High
21 Medium High High High High High High High High High
22 Medium High High High High High High High High High
23 Medium High High High High High High High High High
24 Medium High High High High High High High High High
25 Medium High High High High High High High High High
;
run;


*transpose data to long format;
proc transpose data=grade out=grade_long;
by grade;
var BAL1-BAL10;
run;

*create start and end for balance lookup;
data grade_levels;
set grade_long;
where grade=0;
prev_val = lag(col1);
if _n_=1 then start=0;
else start = prev_val+1;
end = input(col1, 8.);
keep _name_ start end;
run;

*sort for merge;
proc sort data=grade_levels;
by _name_;

proc sort data=grade_long;
by _name_;
run;

*merge balance ranges with categories;
data grade_lookup;
merge grade_long(where=(grade ne 0)) grade_levels;
by _name_;
rename col1=flag;
run;

*sort for merge;
proc sort data=grade_lookup;
by grade start end;
run;


data Acct;  
input acctno Grade Balance;
datalines;
111 1 20000
222 10 14550
9887 18 4065
;
run;

*do lookup;
proc sql;
create table want as
select t1.*, t2.flag
from acct as t1
left join grade_lookup as t2
on t1.grade=t2.grade
and t1.balance between t2.start and t2.end;
quit;

---

@pdata wrote:

Hi Team,

I have TWO Datasets 

    1) GRADE

    2) ACCT

GRADE dataset is kind of a cross table. It tells the Severity -- between Grade values and Balance values

e.g: Grade=1 & BAL6=9000 -- then severity is VeryLow etc

data Grade;
input Grade $ : 15. BAL1 $ BAL2 $ BAL3 $ BAL4 $ BAL5 $ BAL6 $ BAL7 $ BAL8 $ BAL9 $ BAL10 $;
datalines;
0 2200 3500 5000 6250 7500 9000 10500 12500 15000 999999
1 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow
2 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow
3 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow Low Low Low Low
4 VeryLow VeryLow VeryLow VeryLow VeryLow VeryLow Low Low Low Low
5 VeryLow VeryLow VeryLow VeryLow Low Low Low Low Low Medium
6 VeryLow VeryLow VeryLow Low Low Low Low Low Medium Medium
7 VeryLow VeryLow Low Low Low Low Low Medium Medium Medium
8 VeryLow VeryLow Low Low Low Low Low Medium Medium Medium
9 VeryLow Low Low Low Low Low Medium Medium Medium High
10 VeryLow Low Low Low Low Medium Medium Medium Medium High
11 VeryLow Low Low Low Medium Medium Medium Medium High High
12 Low Low Low Medium Medium Medium Medium High High High
13 Low Low Medium Medium Medium Medium High High High High
14 Low Medium Medium Medium Medium High High High High High
15 Low Medium Medium Medium Medium High High High High High
16 Low Medium Medium Medium High High High High High High
17 Low Medium Medium High High High High High High High
18 Medium Medium Medium High High High High High High High
19 Medium Medium High High High High High High High High
20 Medium Medium High High High High High High High High
21 Medium High High High High High High High High High
22 Medium High High High High High High High High High
23 Medium High High High High High High High High High
24 Medium High High High High High High High High High
25 Medium High High High High High High High High High
;
run;

data Acct;  /*Mock Data */
input acctno Grade Balance;
datalines;
111 1 20000
222 10 14550
9887 18 4065
;
run;

Now the WANT dataset should have new column TAG-- holding the Severity values.

acctno  Grade Balance   FLAG
111       1          20000      VeryLow
222     10          14550      Medium
9887   18         4065         Medium
;
run;

Please advice.

Thank You

---

[pdata]

Reeza,

First let me start by saying thank you for the logic. You made it so simple and I was just going though line by line as the approach was driven so nicely and made it so clear.

Thank You so Much.