Solver function to be created in SAS using PROC FCMP

Satyakshma — Wed, 01 Sep 2021 10:42:22 GMT

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

Also, attached is the excel file for more clarity of data.

Looking forward for help as I am new to proc fcmp.

Thanks.

Re: Solver function to be created in SAS using PROC FCMP

Ksharp — Wed, 01 Sep 2021 12:32:05 GMT

Also could try PROC NLIN .

@Rick_SAS wrote many blogs about it .

Re: Solver function to be created in SAS using PROC FCMP

ballardw — Wed, 01 Sep 2021 14:20:33 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

topic Solver function to be created in SAS using PROC FCMP in Mathematical Optimization, Discrete-Event Simulation, and OR

Solver function to be created in SAS using PROC FCMP

Re: Solver function to be created in SAS using PROC FCMP

Re: Solver function to be created in SAS using PROC FCMP