Excel Solver to SAS using PROC FCMP

[Jorge_Ribeiro]

Hello All

I'm trying to replicate in SAS a calculation created using Excel Solver.

The procedure is an iterative solver that adjust the variable Target PD (Probability of Default) by changing the values of the alpha).

I included the excel file.

The input are : Number of counterparties and Probabilities (PD)

[PeterClemmensen]

What arguments should the function / routine accept? What should it return?

[Jorge_Ribeiro]

The alpha indicated in the solver, look :

.

Thanks 

[FreelanceReinh · Accepted solution]

Hello @Jorge_Ribeiro and welcome to the SAS Support Communities!

Try this:

/* Define functions
   JR   to compute PD at t=1
   OPTA to compute alpha for a given target PD
*/

proc fcmp outlib=work.funcs.test;
function JR(NCp[*], PDt0_[*], alpha);
  do i=1 to dim(NCp);
    s + NCp[i]*PDt0_[i]**alpha;
    w + NCp[i];
  end;
  return(s/w);
endfunc;
function opta(NCp[*], PDt0_[*], targetPD);
  a=solve("JR", {.}, targetPD, NCp, PDt0_, .);
  return(a);
endfunc;
run;

/* Make functions available */

options cmplib=work.funcs;

/* Create input dataset for demonstration */

data have;
input NCp PDt0;
retain targetPD 0.04;
format PDt0 targetPD percent8.2;
cards;
10 0.0001
14 0.0005
18 0.01
24 0.02
13 0.05
 5 0.12
 2 0.25
 1 0.40
;

/* Apply function OPTA to dataset HAVE */

data want(keep=targetPD alpha);
array _N[99] _temporary_;
array _P[99] _temporary_;
set have end=last;
_N[_n_]=NCp;
_P[_n_]=PDt0;
if last;
alpha=opta(_N, _P, targetPD);
run;

proc print data=want;
format alpha best12.;
run;

If you change the 0.04 in the RETAIN statement for targetPD to 0.05, you'll get alpha=0.834153... (with a minor difference to the Excel value) as the result. If needed, an "options array" could be added to function OPTA so that you could specify an initial value, convergence criteria, etc. (see documentation of the SOLVE function).

[Jorge_Ribeiro]

Your solution is brilliant.

Thanks

Jorge Ribeiro.

[Ksharp]

I think the best tool for this question is SAS/IML .

@Rick_SAS would love to use IML to solve it.