topic Re: Excel Solver to SAS using PROC FCMP in SAS Programming

Excel Solver to SAS using PROC FCMP

Jorge_Ribeiro — Tue, 13 Sep 2022 09:33:56 GMT

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)

Re: Excel Solver to SAS using PROC FCMP

PeterClemmensen — Tue, 13 Sep 2022 10:26:51 GMT

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

Re: Excel Solver to SAS using PROC FCMP

Jorge_Ribeiro — Tue, 13 Sep 2022 10:31:13 GMT

The alpha indicated in the solver, look :

Thanks

Re: Excel Solver to SAS using PROC FCMP

FreelanceReinh — Tue, 13 Sep 2022 11:59:31 GMT

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).

Re: Excel Solver to SAS using PROC FCMP

Jorge_Ribeiro — Tue, 13 Sep 2022 12:08:47 GMT

Your solution is brilliant.

Thanks

Jorge Ribeiro.

Re: Excel Solver to SAS using PROC FCMP

Ksharp — Tue, 13 Sep 2022 13:20:00 GMT

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

@Rick_SAS would love to use IML to solve it.

Re: Excel Solver to SAS using PROC FCMP

raphaelchaves — Fri, 23 Jan 2026 00:43:34 GMT

/* Solution Using PROC IML */

/* 1. Create the input dataset (same as original) */
data have;
input NCp PDt0;
format PDt0 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
;
run;

/* 2. Solve using PROC IML */
proc iml;
/* --- A. Read Data into Matrix Vectors --- */
use have;
read all var {NCp PDt0};
close have;

/* --- B. Define Constants --- */
targetPD = 0.04;

/* --- C. Define the Function to Solve --- */
/* We want to find alpha such that:
(Weighted Avg PD based on alpha) - TargetPD = 0 */
start DiffFunction(x) global(NCp, PDt0, targetPD);
/* Note: # is element-wise multiplication, ## is element-wise power */
weighted_sum = sum(NCp # (PDt0##x));
total_weight = sum(NCp);

calculated_PD = weighted_sum / total_weight;

/* Return the difference (residual) */
return (calculated_PD - targetPD);
finish;

/* --- D. Find the Root (Alpha) --- */
/* Search for alpha in the range [0, 5]. Adjust this interval if needed. */
intervals = {0 5};

/* FROOT finds the zero of the function defined in "DiffFunction" */
alpha = froot("DiffFunction", intervals);

/* --- E. Output Results --- */
print alpha targetPD;

/* Save to a dataset */
create want var {targetPD alpha};
append;
close want;
quit;

/* Check results */
proc print data=want;
format alpha best12. targetPD percent8.2;
run;