BookmarkSubscribeRSS Feed
Satyakshma
Fluorite | Level 6

Hi,

 

I have to calculated predicted PD 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, and I read that proc optmodel is an equivalent for this in SAS. But I am not able to use proc opt model in SAS EG. Is there any other way I can do this? If yes then please suggest.

If not, then I need help in coding this using Proc opt model as I am not familiar with usage of it

Attached is the Input table and desired output table in excel file:

Table 1: Using Z Score from this

Table 2: Bucket-wise Actual PDs for each year

Table 3: Highlighted yellow values are index values generated, and used in calculating predicted values

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

 

Please help with this, as I am in urgent need to perform this calculation in SAS. 

2 REPLIES 2
RobPratt
SAS Super FREQ

The following does what you want:

data zData;
   input bucket Z_score;
   datalines;
1 -2.38219471237423
2 -0.642830996719877
3 -0.190964264784506
4  0.379313913718413
;

%let minYear = 2015;
%let maxYear = 2018;
data actualData;
   input bucket actual&minYear-actual&maxYear;
   datalines;
1 0.011488175674031 0.006578316410400 0.005081508424610 0.011271589592748
2 0.323791550923876 0.223834610249151 0.180403588330841 0.312637786039641
3 0.500800479192011 0.373187463052586 0.384055738171937 0.439063500503939
4 0.693121340842155 0.592898832157161 0.571297020124314 0.733773269523783
;

proc optmodel;
   set BUCKETS;
   num z_score {BUCKETS};
   read data zData into BUCKETS=[bucket] z_score;

   set YEARS = &minYear...&maxYear;
   num actual {BUCKETS, YEARS};
   read data actualData into [bucket] {y in YEARS} <actual[bucket,y]=col('actual'||y)>;

   var Index {YEARS};
   var Predicted {BUCKETS, YEARS};
   var Error {BUCKETS, YEARS};

   min SSE = sum {b in BUCKETS, y in YEARS} Error[b,y]^2;

   con PredictedCon {b in BUCKETS, y in YEARS}:
      Predicted[b,y] = CDF('NORMAL', Index[y]+z_score[b]);

   con ErrorCon {b in BUCKETS, y in YEARS}:
      Error[b,y] = Predicted[b,y] - actual[b,y];

   solve;

   print actual;
   print Predicted;
   print {y in YEARS} (sum {b in BUCKETS} Error[b,y]^2) Index;
quit;
The SAS System

The OPTMODEL Procedure
Solution Summary
Solver NLP
Algorithm Interior Point Direct
Objective Function SSE
Solution Status Optimal
Objective Value 0.0048361802
   
Optimality Error 7.917181E-10
Infeasibility 3.991606E-10
   
Iterations 2
Presolve Time 0.00
Solution Time 0.01


actual
  2015 2016 2017 2018
1 0.0114882 0.0065783 0.0050815 0.0112716
2 0.3237916 0.2238346 0.1804036 0.3126378
3 0.5008005 0.3731875 0.3840557 0.4390635
4 0.6931213 0.5928988 0.5712970 0.7337733


Predicted
  2015 2016 2017 2018
1 0.0135008 0.0059413 0.0052249 0.0123153
2 0.3184164 0.2188015 0.2057626 0.3058023
3 0.4919160 0.3728249 0.3559350 0.4777037
4 0.7088449 0.5971192 0.5796242 0.6965001


[1]   Index
2015 0.000359105 0.17070
2016 0.000043681 -0.13342
2017 0.001503220 -0.17838
2018 0.002930174 0.13505
Satyakshma
Fluorite | Level 6

Thanks, this works when I checked on my system, but I don't have SAS/OR component installed on SAS EG for office system. Is there a way we can do this using if else loop. As it is the only work around for me right now in absence of SAS/OR

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 801 views
  • 0 likes
  • 2 in conversation