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 "start","end" column of each element
(1)pick up the ith element
(2)for loop all the "start","end" column 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!
-------------------------------------------------------------------------------------------------------------------------------------------
for your information:
(1)the dataset MAP_num and customerScore already exist,and please just concern the point mentioned
(2)the columns named "A","B","C" ...is only for an example and they are all numeric;
(3)Macro is needed for what my colleague asked for ,because MAP_num and customerScore are changing everyday,I mean the column name and data type for them is not changing,but the values for each row are different eveyday that is why I offered two example for table Map_num before.from this point ,macro may also is good choice?
(4)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...
it is a nested loop using SAS macro?as I comprehended.
Why do you think you need a macro for that problem?
It sounds like you are just calculating something using data. Does not look like a code generation problem.
Why are your variables named A,B,C... ? What do they contain?
Do you mean that you really have this data?
TABLE1
cst_id Variable Value
1 A 688567
1 B 873
...
And
TABLE2
variable start end woe beta
A -999999999 57853 -1 3
And you want to combine them and do some calculuations
proc sql ;
create table want as
select a.cusid,a.value,b.*
from table1 a
inner join table2 b
on a.variable = b.variable
;
quit;
thank you for your attention,I have updated the details.pleas check if is it clear enough
1) Is your input data is a sas dataset ? Are all variables numeric?
According to what you have submitted it seems that the data is given in excel sheet.
Then your first mission is to import it into sas data set.
If it is an xlsx type then you can read it directly from the excel sheat using appropriate LIBNAME statement.
libname myxl excel '<path and name>.xlsx';
data temp.
set myxl.<sheet name>;
....
run;
2) Use PROC TRANSFORM to reformat your data from wide to narrow,
moving the variable name to be used as CUS_ID. No need of macro language.
3) Use PROC SORT with NODUPKEY option to get the distict values.
4) It is not clear what next you want to do.
In both tables - the 2nd and the 3rd - you defined:
if table Map_num is below, then cst_id score is update:
but with different formulas.
5) Try to create a sas code then come back to the forum to help you correct your code.
Post your code, the log and describe your problem.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.