## How to read data from the matrix

Solved
Occasional Contributor
Posts: 8

# How to read data from the matrix

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

Accepted Solutions
Solution
‎04-18-2017 08:25 AM
PROC Star
Posts: 8,164

## Re: How to read data from the matrix

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;
Y1_PD=matrix(input(AcctNo,8.),1);
run;
```

Art, CEO, AnalystFinder.com

All Replies
Solution
‎04-18-2017 08:25 AM
PROC Star
Posts: 8,164

## Re: How to read data from the matrix

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;
Y1_PD=matrix(input(AcctNo,8.),1);
run;
```

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 8

## Re: How to read data from the matrix

Thank for your support .. when I run your code .. I faced error : array subscript out of range at line 769 column
PROC Star
Posts: 8,164

## Re: How to read data from the matrix

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

Contributor
Posts: 57

## Re: How to read data from the matrix

Please can you explain how you are calculating Y1_PD values.
PROC Star
Posts: 8,164

## Re: How to read data from the matrix

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

Contributor
Posts: 57

## Re: How to read data from the matrix

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
PROC Star
Posts: 8,164

## Re: How to read data from the matrix

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

Occasional Contributor
Posts: 8

## Re: How to read data from the matrix

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

Occasional Contributor
Posts: 8

## Re: How to read data from the matrix

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 .

Super User
Posts: 13,563

## Re: How to read data from the matrix

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];
drop y1-y12 i rc;
run;```

may need to sort by account if desired.

Occasional Contributor
Posts: 8

## Re: How to read data from the matrix

Thanks alot .. Nice solution
Super User
Posts: 10,784

## Re: How to read data from the matrix

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;

append from want;
close;
quit;

data want;
merge test value;
run;``````
Occasional Contributor
Posts: 8

## Re: How to read data from the matrix

Very Nice Ssolution .. Thank You So much

☑ This topic is solved.