topic Cross Tabulate Data to Create FLAG on Mock Data in SAS Programming

Cross Tabulate Data to Create FLAG on Mock Data

pdata — Wed, 04 May 2022 17:29:56 GMT

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

Re: Cross Tabulate Data to Create FLAG on Mock Data

Reeza — Wed, 04 May 2022 19:37:16 GMT

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: