BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Amr_Alaaeldin
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

13 REPLIES 13
art297
Opal | Level 21

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
Obsidian | Level 7
Thank for your support .. when I run your code .. I faced error : array subscript out of range at line 769 column
art297
Opal | Level 21

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
Quartz | Level 8
Please can you explain how you are calculating Y1_PD values.
art297
Opal | Level 21

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
Quartz | Level 8
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
Opal | Level 21

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
Obsidian | Level 7

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

Amr_Alaaeldin
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7
Thanks alot .. Nice solution 🙂
Ksharp
Super User

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
Obsidian | Level 7

Very Nice Ssolution .. Thank You So much 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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