topic Iterative excel solver in sas in SAS Programming

Iterative excel solver in sas

Satyakshma — Mon, 06 Sep 2021 17:50:34 GMT

Hello All

I'm trying to replicate in SAS a procedure I use in Excel. This procedure is basically an iterative solver (through a macro) that puts the variable Delta (Sum square error of actual and predicted value) to minimum value by changing the values of the Index value(starting with null values) used to calculate predict value.

The input data start like this:

bucket	score	actual	predicted	delta	index
1	-2.38219	0.011488	.	.	.
2	-0.64283	0.323792	.	.	.
3	-0.19096	0.5008	.	.	.
4	0.379314	0.693121	.	.	.

- bucket, score and actual are inputs;

- index values are generated by Excel; Used in calculating predicted which is further used to find the delta

- predicted is given by NORM.S.DIST(score + index, TRUE)

- Delta is minimum sum square error (actual, predicted)

The solver objective function is Delta, it operates by changing index values and the constraint is predicted value should be such that minimum delta value. It runs for each cell.

After solver macro, the desired output is:

bucket	score	actual	predicted	delta	index
1	-2.38219	0.011488	0.013501	0.000359105	0.170699
2	-0.64283	0.323792	0.318416	0.000359105	0.170699
3	-0.19096	0.5008	0.491916	0.000359105	0.170699
4	0.379314	0.693121	0.708845	0.000359105	0.170699

Is there a way to do this in SAS except for proc optmodel as I don't have license for SAS/OR?

I have been trying and searching for a few days, without success.

If anyone could help with the code for this, it would be very much helpful and appreciated.

Kind Regards,

Satyakshma Rawat

Re: Iterative excel solver in sas

Reeza — Tue, 07 Sep 2021 01:29:09 GMT

Yes, this is doable in SAS in a data step. You should probably start by providing data as data steps and trying to solve some of it yourself though. At least some of the basic stuff - ie generating the random values and getting the normal distribution calcs. Basically get the first iteration done and then we can show you how to iterate it to get it all working.

Re: Iterative excel solver in sas

Satyakshma — Tue, 07 Sep 2021 01:39:21 GMT

Hi Reeza,

I am facing problem in generating random values itself. I am not able to identify the way to generate random values using which I can do normal distribution calculations.

Re: Iterative excel solver in sas

Satyakshma — Tue, 07 Sep 2021 01:51:51 GMT

As the generation of random values to calculate the normal distribution is dependent on the constraint that delta value should be minimum. As I am new to SAS, I am facing difficulty in writing code to satisfy the constraint and generate results.