pseudocode into SAS macro code

Geo- — Mon, 05 Mar 2018 06:42:19 GMT

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.

Re: pseudocode into SAS macro code

Tom — Mon, 05 Mar 2018 05:48:30 GMT

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;

Re: pseudocode into SAS macro code

Shmuel — Mon, 05 Mar 2018 06:01:52 GMT

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.

Re: pseudocode into SAS macro code

Geo- — Mon, 05 Mar 2018 06:36:09 GMT

thank you for your attention,I have updated the details.pleas check if is it clear enough

topic Re: pseudocode into SAS macro code in SAS Programming

pseudocode into SAS macro code

Re: pseudocode into SAS macro code

Re: pseudocode into SAS macro code

Re: pseudocode into SAS macro code