I am not familiar with SAS base and macro language syntax ,my codes keep going wrong..can someone offer a piece of SAS macro code of my pseudocode.
1.create a macro array to store all the distinct variable in table Map_num;
select distinct variable:into numVarList separated by ' ' from Map_num;
quit;
2.for loop the macro array numVarList and for loop each value of each element
(1)pick up the ith element
(2)for loop all the value of the ith element,
(3)if the value of the customer (from customerScore table)is within the scale of "start" and "end",then update score=score+woe*beta
for example:
the customerScore table is:
+--------+--------+---------+---------+----------+---------+---------+---------+---------+---------+---------+---------+-------+
| cst_id | A | B | C | D | E | F | G | H | I | J | K | score |
+--------+--------+---------+---------+----------+---------+---------+---------+---------+---------+---------+---------+-------+
| 1 | 688567 | 873 | 134878 | 546546 | 3123 | 6 | 5345 | 768678 | 348957 | -921839 | -8217 | 0 |
| 2 | 3198 | 54667 | 9789867 | 53456756 | 78978 | 6456 | 645 | 534 | -219 | 13312 | 4543 | 0 |
| 3 | 35324 | 6456568 | 43 | 56756 | -8217 | 688567 | 873 | 134878 | 12 | 89173 | 213142 | 0 |
| 4 | 348957 | -921839 | -8217 | 5345 | 434534 | 3198 | 54667 | 9789867 | -8217 | -8217 | 8908102 | 0 |
| 5 | -219 | 13312 | 4543 | 4234 | 54667 | 35324 | 6456568 | 43 | 213142 | 213142 | 213 | 0 |
| 6 | 12 | 89173 | 213142 | 23234 | 348957 | -921839 | -8217 | 688567 | 873 | 134878 | 23424 | 0 |
| 7 | 688567 | 89173 | 213142 | -8217 | -219 | 13312 | 4543 | 3198 | 54667 | 9789867 | 3434 | 0 |
| 8 | 3198 | -8217 | 21313 | -8217 | 12 | 89173 | 213142 | 35324 | 6456568 | 43 | 3123 | 0 |
| 9 | 35324 | -8217 | 688567 | 688567 | 873 | 134878 | 688567 | 873 | 134878 | -8217 | 11 | 0 |
| 10 | 348957 | 89173 | 213142 | 3198 | 54667 | 9789867 | 3198 | 54667 | 9789867 | -8217 | 3198 | 0 |
| 11 | -219 | -921839 | -8217 | 35324 | 6456568 | 43 | 35324 | 6456568 | 43 | -921839 | -8217 | 0 |
| 12 | 12 | 13312 | 4543 | 89173 | 4234 | 3198 | 688567 | 873 | 134878 | 13312 | 4543 | 0 |
| 13 | 12 | 89173 | 213142 | 348957 | -921839 | -8217 | 3198 | 54667 | 9789867 | 89173 | 213142 | 0 |
| 14 | 2 | 89173 | 213142 | -219 | 13312 | 4543 | 35324 | 6456568 | 43 | 54667 | 4543 | 0 |
| 15 | 348957 | -921839 | -8217 | 12 | 89173 | 213142 | 13312 | 4543 | 89173 | 4234 | 4543 | 0 |
| 16 | -219 | 13312 | 35324 | 6456568 | 43 | 213142 | 89173 | 213142 | 348957 | -921839 | -8217 | 0 |
| 17 | 12 | 89173 | -921839 | -8217 | 688567 | 873 | 89173 | 213142 | -219 | 13312 | 4543 | 0 |
| 18 | 688567 | 873 | 13312 | 4543 | 3198 | 54667 | -921839 | -8217 | 12 | 89173 | 213142 | 0 |
| 19 | 3198 | 54667 | 9789867 | 688567 | 873 | 134878 | 43 | 213142 | 213142 | 213 | 9789867 | 0 |
| 20 | 35324 | 6456568 | 43 | 43 | 213142 | 213142 | 213 | 89173 | 4234 | 3198 | 688567 | 0 |
+--------+--------+---------+---------+----------+---------+---------+---------+---------+---------+---------+---------+-------+
if table Map_num is below,then cst_id score is update:score=0+(-1.2)*3 + 2*3 + (0.1)*3 + 7*3
+----------+------------+------------+------+------+
| variable | start | end | woe | beta |
+----------+------------+------------+------+------+
| A | -999999999 | 57853 | -1 | 3 |
| A | 57853 | 89756 | -1.1 | 3 |
| A | 89756 | 897452 | -1.2 | 3 |
| A | 897452 | 9999999999 | -1.3 | 3 |
| B | -999999999 | 4235 | 2 | 3 |
| B | 4235 | 65785 | 3 | 3 |
| B | 65785 | 9999999999 | 4 | 3 |
| C | -999999999 | 9673 | 3.1 | 3 |
| C | 9673 | 75341 | 2.1 | 3 |
| C | 75341 | 98543 | 1.1 | 3 |
| C | 98543 | 567864 | 0.1 | 3 |
| C | 567864 | 9999999999 | -1 | 3 |
| D | -999999999 | 8376 | 5 | 3 |
| D | 8376 | 93847 | 6 | 3 |
| D | 93847 | 9999999999 | 7 | 3 |
+----------+------------+------------+------+------+
if table Map_num is below,then cst_id score is update:score=0+3*2 + 5*2 + 0*2 + 7*2 +3*2
+----------+------------+------------+-----+------+
| variable | start | end | woe | beta |
+----------+------------+------------+-----+------+
| E | -999999999 | 3 | 1 | 2 |
| E | 3 | 500000 | 3 | 2 |
| E | 500000 | 800000 | 2 | 2 |
| E | 800000 | 9999999999 | 4 | 2 |
| A | -999999999 | 6700 | 6 | 2 |
| A | 590000 | 680000 | 4 | 2 |
| A | 680000 | 9999999999 | 5 | 2 |
| C | -999999999 | 89678 | 9 | 2 |
| C | 89678 | 566757 | 0 | 2 |
| C | 566757 | 986785 | 2.8 | 2 |
| C | 986785 | 9999999999 | 1.1 | 2 |
| K | -999999999 | 7865 | 7 | 2 |
| K | 7865 | 25637 | 9 | 2 |
| K | 25637 | 65742 | 8 | 2 |
| K | 65742 | 9999999999 | 0.2 | 2 |
| B | -999999999 | 56753 | 3 | 2 |
| B | 56753 | 5465624 | 4 | 2 |
| B | 5465624 | 9999999999 | 1 | 2 |
+----------+------------+------------+-----+------+
thanks in advance!
table customerScore and Map_num are changing everyday for each rows and their column name:variable,start,end,woe,beta are not changed.I need to update the column score in table customerScore and the score is according to table Map_num.If the column A value in table customerScore is 688567 ,so it is 89756 <688567<897452,so the socre will be update:score=score+(-1.2 )* 3...is that clear for you?! it is a nested loop using SAS macro as I comprehended.
Unfortunately the customerScore is not in a form that is readily aligned for a really simple SQL computation.
SQL way
One important aspect is to recognize the selection of map and woe for each score part from map_num can be done relatively easily in SQL, but processing the individual variables has to be 'coaxed' through macro
Consider only the variable A from the first map_num as a example case.
select (
map_num.woe * map_num.beta
from map_num
where map_num.variable="A"
and map_num.start < customerScore.A <= mapnum.end
) as A_contribution_to_score
from
customerScore
Now consider the B contribution that is added to the overall expression
select (
map_num.woe * map_num.beta
from map_num
where map_num.variable="A"
and map_num.start < customerScore.A <= mapnum.end
)
+
select (
map_num.woe * map_num.beta
from map_num
where map_num.variable="B"
and map_num.start < customerScore.B <= mapnum.end
)
from
customerScore
You should see that a macro could determine the distinct map_num values of variable to be used to construct a rather lengthy SQL expression that searches for the appropriate woe and beta product to apply to each row in customerScore.
The macro and SQL update statement could be something like
%macro updateScore (data=, map=);
%local i n_var;
proc sql noprint;
select distinct variable into :variable1- from ↦
%let N_var = &sqlobs;
update &data as OUTER
set score = score
%do I = 1 %to &N_var;
%let variable = &&variable&i;
+
( select
INNER.woe * INNER.beta
from &map as INNER
where INNER.variable="&variable"
and INNER.start < OUTER.&variable <= INNER.end
)
%end;
; /* end of update statement */
quit;
%mend;
%updateScore(data=customerScore, map=map_num)
Your data structure needs a bit of work if you want the score update made via a map_num to be reversible (i.e. capable of having an undo action applied).
If tracking the map selections is important you would want an additional similar query in the macro that creates a table recording the important aspects of the map data selection
create table mapplication as
select cst_id
%do I = 1 %to &N_var;
%let variable = &&variable&i;
%let innerness = from &map as INNER where INNER.variable="&variable" and INNER.start < OUTER.&variable <= INNER.end;
, &variable
, ( select INNER.woe &innerness ) as &variable._woe
, ( select INNER.beta &innerness ) as &variable._beta
, ( select INNER.start &innerness ) as &variable._start
, ( select INNER.end &innerness ) as &variable._end
%end;
from &data as OUTER;
Examining the 'mapplication' data can possibly help diagnose bad map_num data.