DATA Step, Macro, Functions and more

Create code with SAS Macro

Reply
Occasional Contributor
Posts: 6

Create code with SAS Macro

I have SAS code that returns variables with their bins scores. After that, new variables are created based on their bins score. I want to automate this process.

For example, below is the table that is used to create new variables with suffix "_sc".

VariableMin_BinMax_BinScore
Avg_Balance_last_6M25500.06
Avg_Balance_last_6M51750.05
Avg_Balance_last_6M761000.07
Fee_1_3M0.0050.0090.007
Fee_1_3M0.010.030.06
Fee_1_3M0.030.050.02
Fee_1_3M0.050.070.03

Manual Code :

IF Avg_Balance_last_6M >= 25 then Avg_Balance_last_6M_sc = 0.06;

IF Avg_Balance_last_6M >= 51 then Avg_Balance_last_6M_sc = 0.05;

IF Avg_Balance_last_6M >= 76 then Avg_Balance_last_6M_sc = 0.07;

IF Fee_1_3M >= 0.005 then Fee_1_3M_sc = 0.007;

IF Fee_1_3M >= 0.01 then Fee_1_3M_sc = 0.06;

IF Fee_1_3M >= 0.03 then Fee_1_3M_sc = 0.02;

IF Fee_1_3M >= 0.05 then Fee_1_3M_sc = 0.03;

Super User
Posts: 10,466

Re: Create code with SAS Macro

I would be very tempted to use the information in your first table to create custom format or informat with the Min_Bin variable as START, Max_bin as End and Score as Label. Then I could either just use the formatted value of the existing variable or make an informat to create the variables with.

data formatcontrol;

     set have (rename=(Variable=FMTName); /* if any of the variables end in a numeral then we'll need to address that*/

     Start= put(Min_bin, best8.);

     End = put(Max_bin,Best8.);

     Label = put (score,z9.7);

run;

proc format cntlin=formatcontrol;

run;

Then you use the formats such as :

Proc freq data=have;

table Avg_Balance_last_6M Fee_1_3M;

format Avg_Balance_last_6M Avg_Balance_last_6M. Fee_1_3M Fee_1_3M.;

run;

Or create an informat and then

use Fee_1_3M_sc = input (Fee_1_3M,Fee_1_3M.);

Super User
Posts: 5,071

Re: Create code with SAS Macro

I strongly recommend that you don't do this alone.  Find a local senior SAS programmer who can guide you through some of the issues.  Based on what you have posted so far, here are two issues that you may want to consider.

1. What if a variable name is already 30 characters long?  There's won't be room to add "_rc" to the end of it.  How should that be handled?

2. In an automated system, it is often important to make the code as speedy as possible.  The manual code  you have posted may run quickly enough, or you may want to speed it up by adding ELSE.  For example:

IF Avg_Balance_last_6M >= 76 then Avg_Balance_last_6M_sc = 0.07;

else IF Avg_Balance_last_6M >= 51 then Avg_Balance_last_6M_sc = 0.05;

else IF Avg_Balance_last_6M >= 25 then Avg_Balance_last_6M_sc = 0.06;

I would judge it extremely likely that a senior programmer would find additional issues and shortcuts, and would definitely recommend that route.

Good luck.

Contributor
Posts: 44

Re: Create code with SAS Macro

I recommend using call execute for this:

Riya88 wrote:

I have SAS code that returns variables with their bins scores. After that, new variables are created based on their bins score. I want to automate this process.

For example, below is the table that is used to create new variables with suffix "_sc".

Variable Min_Bin Max_Bin Score
Avg_Balance_last_6M 25 50 0.06
Avg_Balance_last_6M 51 75 0.05
Avg_Balance_last_6M 76 100 0.07
Fee_1_3M 0.005 0.009 0.007
Fee_1_3M 0.01 0.03 0.06
Fee_1_3M 0.03 0.05 0.02
Fee_1_3M 0.05 0.07 0.03

so make a dataset (rules) from this data. In this example I'm assuming that you are having your original data in dataset "source".

Data _null_;

  set rules end=last;

if _n_=1 then call execute ('Data source; set source;');

call execute ('if '||strip(variable)||' GE '||strip(min_bin) ||' then '||strip(variable)||'_SC = '||strip(score) ||';');

if last then call execute (' run;');

run;

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