DATA Step, Macro, Functions and more

How to read data from the matrix

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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 .. 

 

AcctNoBalanceDelq_RatingLoan_Years_RemainingY1_PDLifeTime_PD
000000110000120.0210.065
000000215000230.0880.282
000000320000610.1270.275
000000425000350.2010.472
000000530000460.2420.596
000000635000550.2750.602
000000740000890.3931.000
000000845000741.0000.642

 

 

Thanks in Advance


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

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;
  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

 

View solution in original post


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

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;
  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

 

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: 7,363

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: 56

Re: How to read data from the matrix

Please can you explain how you are calculating Y1_PD values.
PROC Star
Posts: 7,363

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: 56

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: 7,363

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: 10,497

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];
   LifeTime_Pd = y(Loan_Years_Remaining); 
   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 Smiley Happy
Super User
Posts: 9,676

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;

create value from want[c={Y1_PD	LifeTime_PD}];
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 231 views
  • 3 likes
  • 5 in conversation