The LOAN procedure does not support a DATA= option to allow for processing a portfolio of loans. In the case of fixed rate loans, the MORT function in the DATA step is often a reasonable alternative when a data set containing fixed rate loan parameters needs to be processed. However, when more complex loans such as adjustable rate mortgages need to be analyzed, a general DATA step solution is not feasible.

If you can define a PROC LOAN step that can be consistently applied to all observations in a data set, where only the values of the loan parameters vary from observation to observation, then you can use a combination of PROC LOAN and the CALL EXECUTE statement in the DATA step to simulate the functionality of a DATA= option. Prior to using CALL EXECUTE in a DATA step, a simple macro must be defined which specifies the macro parameters to be passed to the PROC LOAN options.

The following example illustrates how to compute the monthly payment for each of two fixed rate loans in a portfolio. The parameters of each loan are specified in an observation in data set LOANS. The MORT function is used to compute the monthly payment for each of the loans. Note that the MORT function expects the interest rate to be expressed as a periodic, fractional rate, whereas the LOAN procedure expects the interest rate to be expressed as an annual percentage. The RATE variable in the input data set contains the annual percentage rate for each loan.

   data loans;
     input acct_id amt rate life;
     payment=mort(amt , . , (rate/100)/12 , life);
     datalines;
   1 495250 8.25  360
   2 13500  9.125 360
   ;
   
   proc print data=loans noobs;
     run;

Following is the data set of loan parameters and the payment amounts computed by the MORT function.

Next, a macro is defined which is called by the CALL EXECUTE statement to build the PROC LOAN statements needed to compute the monthly payment for each of the two loans. The PRTFOLIO macro specifies the PROC LOAN step that needs to be run for each observation in data set LOANS with macro variables in place of the values that will be read from each observation. The following DATA step uses the CALL EXECUTE statement to call the PRTFOLIO macro for each observation. Inside the CALL EXECUTE statement, a text string is created for each observation by concatenating the text of the macro call with the loan values that need to be inserted into the call. When the DATA step runs, the PRTFOLIO macro is then executed once for each loan using that loan's parameters.

   %macro prtfolio(amt,rate,life);
      proc loan;
        fixed amount = &amt rate=&rate life=&life;
        run;
   %mend;
   
   data _null_;
      set loans;
      call execute('%prtfolio(' || trim(left(amt))  || ',' 
                                || trim(left(rate)) || ',' 
                                || trim(left(life)) || ')' 
                  );
   run;

Below are the results of running the DATA step. Notice that the payment values computed by the PROC LOAN steps built by CALL EXECUTE agree with the values computed previously by the MORT function.

A more complex example follows illustrating how to use CALL EXECUTE to process a data set of adjustable rate loans with a prepayment and a rate adjustment.

   data loans;
     input acct_id amt rate life period_ra1 rate_ra1 period_prep amt_prep label $18.;
     datalines;
   1 495250 8.25  360 24 10.25   24 100000 ex1 prepay 2 years
   1 495250 8.25  360 24 10.25   48 200000 ex1 prepay 4 years
   2 13500  9.125 360 24  9.3332 24   7000 ex2 prepay 2 years
   2 13500  9.125 360 24  9.3332 48   8000 ex2 prepay 4 years
   ;
   
   proc print data=loans noobs;
     run;
   
   %macro prep_ra(amt,rate,life,pra1,rra1,pprep,aprep,id,label);
      proc loan;
        arm amount = &amt rate=&rate life=&life
            estimatedcase =(&pra1=&rra1) 
            prep = (&pprep=&aprep) label = "&&id: &&label";
        run;
   %mend;
   
   data _null_;
      set loans;
      call execute('%prep_ra(' || trim(left(amt))      || ',' || trim(left(rate))        || ','  
                               || trim(left(life))     || ',' || trim(left(period_ra1))  || ','
                               || trim(left(rate_ra1)) || ',' || trim(left(period_prep)) || ','
                               || trim(left(amt_prep)) || ',' || trim(left(acct_id))     || ','
                               || trim(left(label))    || ')'
                  );
      run;

Following is a listing of the data set of loan parameters and the results from the four PROC LOAN steps analyzing the four loans in this portfolio.

For more information on the LOAN procedure, see the SAS/ETS User's Guide. For more information on CALL EXECUTE, see SAS Macro Language: Reference in Base SAS documentation.

Operating System and Release Information