Create solver to get minimum sum square error using PROC FCMP

[Satyakshma]

Hello Everyone,

I have to calculate the predicted values for year 2015, 2016 and 2017 for Bucket1, Bucket2, Bucket3 and Bucket4 using a Z_score and an Index value. I already have Z_Score from other table, but I need to generate an Index value (which is a random value).
The Index value to be generated should be such that that when we calculate predicted PD using it for each year. The sum square errors of Predicted PDs and Actual PDs should be minimum.

I am able to do this in Excel using Run solver function, and I need to do this using PROC FCMP in BASE SAS in SAS EG as I don't have SAS/IML or SAS/OR COMPONENT. Please help me with this code as I am new to PROC FCMP.

Attached is the Input table and desired output table:

Table 1: Using Z Score from this

Col1	Value 1	Z value
1	0.86%	-2.38
2	26.02%	-0.64
3	42.43%	-0.19
4	64.78%	0.38

Table 2: Bucket-wise Actual values for each year

Col1	2015	2016	2017	2018
1	1.15%	0.66%	0.51%	1.13%
2	32.38%	22.38%	18.04%	31.26%
3	50.08%	37.32%	38.41%	43.91%
4	69.31%	59.29%	57.13%	73.38%
5	99.72%	100.00%	100.00%	100.00%

Table 3: Index values are random values generated, and used in calculating predicted values in bold

Index value	0.100000	0.170699	-0.133416	-0.178382	0.135047
Col1		2015	2016	2017	2018
1		1.35%	0.59%	0.52%	1.23%
2		31.84%	21.88%	20.58%	30.58%
3		49.19%	37.28%	35.59%	47.77%
4		70.88%	59.71%	57.96%	69.65%

Table 4: Min sum of squares (between actual values and predicted values) value and Index value.

	Sum of Squares	Index
	0.000
2015	0.000	0.17
2016	0.000	-0.13
2017	0.002	-0.18
2018	0.003	0.14

I have written the below code using proc fcmp for the above problem, but I am not able to create the right code for this:

PROC FCMP OUTLIB=work.functions.ci;

function predict(currentValue);
return(CDF('NORMAL', currentValue + index));
endsub;

function sum_square_error(value1,value2);
diff = (value1-value2);
return(diff*diff);
endsub;

function minimum(y,sse,index,value1,value2);
return(solve("sum_square_error",.,y,min(sse));
endsub;

QUIT;

options cmplib=( work.functions);

data test;
set test;
predicted = cred_index(Z_score);
err = sum_square_error(actual,predicted);
run;

Please help with this problem.

[Sajid01]

Please have a look at http://support.sas.com/resources/papers/proceedings13/139-2013.pdf .
This will help understand Proc FCMP.

[Patrick]

I would first write the logic using data step Base SAS syntax. Only once you've got the logic right I'd start converting this to a function using Proc FCMP.

Getting sample data (created via a SAS data step) and the working data step logic would also help us to support you with the Proc FCMP implementation.

[Satyakshma]

I can create a simple dataset using datastep but I am not able to create datastep logic to create index value which is used to create predicted value with a condition that sum square error should be minimum.

[Patrick]

---

@Satyakshma wrote:
I can create a simple dataset using datastep but I am not able to create datastep logic to create index value which is used to create predicted value with a condition that sum square error should be minimum.

---

There is to my knowledge no logic using Proc FCMP that you couldn't implement directly within a SAS data step - but it should be easier to implement and debug in a SAS data step. That's why I'm proposing data step first, conversion to Proc FCMP second.

Populating an index variable with a random value would be simple but I believe there might be a bit more to your problem and I don't have the subject matter expertise. Calling on other people who might know more. @Rick_SAS @Ksharp @PGStats @FreelanceReinh 

[Satyakshma]

Hi Patrick,
As per my understanding so far in SAS, I can generate random value using RAND( ) function. But my problem statement comes with one condition: this random value is to be used to calculate a "predicted values" as shown in Table 3 such that there is "minimum sum square difference" between these predicted value in Table 3 and actual value in Table 2.
I am not able to create a logic around, how satisfy this condition in order to get final output.
@Rick_SAS @Ksharp @PGStats @FreelanceReinhard It would be really helpful if someone can help me with the code as I am really in need to develop SAS code around this.

[Satyakshma]

@Rick_SAS @Ksharp @PGStats @FreelanceReinhard @Patrick Tourmaline
If this can be done in data step then this will also work for me.

[Ksharp]

You mean this ?
https://communities.sas.com/t5/Mathematical-Optimization/get-a-value-after-minimizing-sum-of-squares...