How to read data from the matrix

[Amr_Alaaeldin]

I need to select  PD% from the below matrix in another data set based on the delinquency loan years remaining 

For exmple : if the loan Account Dlq rating = 2 and The Years remaining to maturity is 3 years 

so LifeTime_PD% should be 10% as shown below .  

and Y1_PD always from Y1 Column .  

       Y1       Y2      Y3  etc.

1

2                          10%

3

4

etc.

Exmple :

/*DataSet 1*/

Data test ;
length AcctNo $10.;
input AcctNo Balance Delq_Rating Loan_Years_Remaining Y1_PD LifeTime_PD;
Datalines ;
0000001 10000 1 2 0 0
0000002 15000 2 3 0 0
0000003 20000 6 1 0 0
0000004 25000 3 5 0 0
0000005 30000 4 6 0 0
0000006 35000 5 5 0 0
0000007 40000 8 9 0 0
0000008 45000 7 4 0 0
;
Run ;

/*DataSet 2*/

Data matrix ;
input Delq_Rating Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10 Y11 Y12;
Datalines ;
1 0.021 0.065 0.121 0.182 0.243 0.302 0.357 0.410 0.458 0.503 0.544 0.582
2 0.088 0.194 0.282 0.355 0.416 0.469 0.515 0.557 0.595 0.629 0.660 0.688
3 0.127 0.247 0.340 0.413 0.472 0.522 0.565 0.603 0.637 0.668 0.696 0.721
4 0.201 0.338 0.430 0.499 0.552 0.596 0.633 0.666 0.695 0.721 0.745 0.766
5 0.242 0.387 0.483 0.551 0.602 0.643 0.678 0.707 0.733 0.756 0.777 0.795
6 0.275 0.421 0.515 0.582 0.631 0.670 0.702 0.730 0.754 0.775 0.794 0.812
7 0.393 0.510 0.587 0.642 0.683 0.716 0.743 0.766 0.787 0.805 0.822 0.837
8 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000
;
Run ;

Result Should be .. 

AcctNo	Balance	Delq_Rating	Loan_Years_Remaining	Y1_PD	LifeTime_PD
0000001	10000	1	2	0.021	0.065
0000002	15000	2	3	0.088	0.282
0000003	20000	6	1	0.127	0.275
0000004	25000	3	5	0.201	0.472
0000005	30000	4	6	0.242	0.596
0000006	35000	5	5	0.275	0.602
0000007	40000	8	9	0.393	1.000
0000008	45000	7	4	1.000	0.642

Thanks in Advance

[art297 · Accepted solution]

Not sure if I correctly understand what you're trying to do, but the following matches your desired result:

data want (drop=i DRating y1-y12);
  array matrix(8,12) _temporary_;
  if _n_ eq 1 then do until(eof1);
    set matrix (rename=(Delq_Rating=DRating)) end=eof1;
    array yvalues(12) y1-y12;
    do i=1 to 12;
      matrix(DRating,i)=yvalues(i);
    end;
  end;
  set test (drop=Y1_PD LifeTime_PD);
  Y1_PD=matrix(input(AcctNo,8.),1);
  LifeTime_PD=matrix(Delq_Rating,Loan_Years_Remaining);
run;

Art, CEO, AnalystFinder.com

[Amr_Alaaeldin]

Thank for your support .. when I run your code .. I faced error : array subscript out of range at line 769 column

[art297]

That is probably because your real data isn't like your example. What decides which row of the matrix should be looked up. I based it on the numeric equivalent of account number which, in your case, was 1 thru 8. If it's something else, the code has to be changed. However, the general algorithm is what I think you need.

Art, CEO, AnalystFinder.com

[lakshmi_74]

Please can you explain how you are calculating Y1_PD values.

[art297]

My code was calculating them from the ID numbers, which went from 1 to 8. That matched your example data but, apparently, you have some other criterion that specifies where they are supposed to come from.

Art, CEO, AnalystFinder.com

[lakshmi_74]

Yes, I also found the same. But probability of default is not calculated depend on customer number. So there might be some other calculation is there for the Y1_PD

[art297]

The OP (original poster) hasn't told us where it is supposed to come from

When you responded I assumed YOU were the OP. Guess we'll have to wait.

Art, CEO, AnalystFinder.com

[Amr_Alaaeldin]

Hi .. sorry for the late reply but i had an emergency and i cannot access my laptop just now 

[Amr_Alaaeldin]

Hi .. in this example the PD% is already calculated based on historical data for 5 Years 

I thik the below document will guide you 

https://www.math.kth.se/matstat/seminarier/reports/M-exjobb14/140908.pdf 

The result located in Chapter 5 . 

[ballardw]

Maybe:

data work.want;
   length Delq_Rating Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10 Y11 Y12 8;
   if _N_ = 1 then do; 
      /* Load lookup data set into the hash object */ 
      declare hash h(dataset: "work.matrix", hashexp: 6); 
      /* Define the data set lookup's variable emp_nbr as key and emp_nm as value */
      h.defineKey('Delq_Rating'); 
      h.defineData('Delq_Rating','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9','Y10','Y11','Y12'); 
      h.defineDone(); 
      call missing(Delq_Rating, Y1,Y2,Y3,Y4,Y5,Y6,Y7,Y8,Y9,Y10,Y11,Y12); 
   end; 
   set work.test;
   array y y1-y12;
   do i=1 to dim(y);
      rc=h.find();
   end;
   Y1_Pd= y[1];
   LifeTime_Pd = y(Loan_Years_Remaining); 
   drop y1-y12 i rc;
run;

may need to sort by account if desired.

[Amr_Alaaeldin]

Thanks alot .. Nice solution 🙂

[Ksharp]

It is very easy for IML code.

Data test ;
length AcctNo $10.;
input AcctNo Balance Delq_Rating Loan_Years_Remaining Y1_PD LifeTime_PD;
Datalines ;
0000001 10000 1 2 0 0
0000002 15000 2 3 0 0
0000003 20000 6 1 0 0
0000004 25000 3 5 0 0
0000005 30000 4 6 0 0
0000006 35000 5 5 0 0
0000007 40000 8 9 0 0
0000008 45000 7 4 0 0
;
Run ;
 

Data matrix ;
input Delq_Rating Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 Y10 Y11 Y12;
Datalines ;
1 0.021 0.065 0.121 0.182 0.243 0.302 0.357 0.410 0.458 0.503 0.544 0.582
2 0.088 0.194 0.282 0.355 0.416 0.469 0.515 0.557 0.595 0.629 0.660 0.688
3 0.127 0.247 0.340 0.413 0.472 0.522 0.565 0.603 0.637 0.668 0.696 0.721
4 0.201 0.338 0.430 0.499 0.552 0.596 0.633 0.666 0.695 0.721 0.745 0.766
5 0.242 0.387 0.483 0.551 0.602 0.643 0.678 0.707 0.733 0.756 0.777 0.795
6 0.275 0.421 0.515 0.582 0.631 0.670 0.702 0.730 0.754 0.775 0.794 0.812
7 0.393 0.510 0.587 0.642 0.683 0.716 0.743 0.766 0.787 0.805 0.822 0.837
8 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000
;
Run ;
proc iml;
use test nobs n;
read all var {Delq_Rating Loan_Years_Remaining } ;
close;

use matrix(drop=Delq_Rating);
read all var _all_ into y;
close;

v=j(n,1,.);
do i=1 to n;
 v[i]=y[Delq_Rating[i],Loan_Years_Remaining[i]];
end;
want=y[,1]||v;

create value from want[c={Y1_PD	LifeTime_PD}];
append from want;
close;
quit;

data want;
 merge test value;
run;

[Amr_Alaaeldin]

Very Nice Ssolution .. Thank You So much