DATA Step, Macro, Functions and more

pseudocode into SAS macro code

Reply
Contributor
Posts: 36

pseudocode into SAS macro code

[ Edited ]

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_idABCDEFGHIJKscore
1688567873134878546546312365345768678348957-921839-82170
2319854667978986753456756789786456645534-2191331245430
33532464565684356756-821768856787313487812891732131420
4348957-921839-821753454345343198546679789867-8217-821789081020
5-219133124543423454667353246456568432131422131422130
6128917321314223234348957-921839-8217688567873134878234240
768856789173213142-8217-219133124543319854667978986734340
83198-821721313-821712891732131423532464565684331230
935324-8217688567688567873134878688567873134878-8217110
103489578917321314231985466797898673198546679789867-821731980
11-219-921839-82173532464565684335324645656843-921839-82170
121213312454389173423431986885678731348781331245430
131289173213142348957-921839-82173198546679789867891732131420
14289173213142-219133124543353246456568435466745430
15348957-921839-8217128917321314213312454389173423445430
16-219133123532464565684321314289173213142348957-921839-82170
171289173-921839-821768856787389173213142-2191331245430
18688567873133124543319854667-921839-821712891732131420
1931985466797898676885678731348784321314221314221397898670
20353246456568434321314221314221389173423431986885670

 

 

if table Map_num is below,then cst_id score is update:score=0+(-1.2)*3 + 2*3 + (0.1)*3 + 7*3

variablestartendwoebeta
A-99999999957853-13
A5785389756-1.13
A89756897452-1.23
A8974529999999999-1.33
B-999999999423523
B42356578533
B65785999999999943
C-99999999996733.13
C9673753412.13
C75341985431.13
C985435678640.13
C5678649999999999-13
D-999999999837653
D83769384763
D93847999999999973

 

if table Map_num is below,then cst_id score is update:score=0+3*2 + 5*2 + 0*2 + 7*2 +3*2

variablestartendwoebeta
E-999999999312
E350000032
E50000080000022
E800000999999999942
A-999999999670062
A59000068000042
A680000999999999952
C-9999999998967892
C8967856675702
C5667579867852.82
C98678599999999991.12
K-999999999786572
K78652563792
K256376574282
K6574299999999990.22
B-9999999995675332
B56753546562442
B5465624999999999912

 

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.

Super User
Super User
Posts: 7,938

Re: pseudocode into SAS macro code

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;
Contributor
Posts: 36

Re: pseudocode into SAS macro code

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

Trusted Advisor
Posts: 1,831

Re: pseudocode into SAS macro code

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.

 

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 83 views
  • 0 likes
  • 3 in conversation