Working with an upper-triangular array in SAS (challenge +2 Points)

Question

I'm looking to improve my code efficiency by turning my code into arrays and loops. The data i'm working with starts off like this:

ID    Mapping    Asset    Fixed    Performing    Payment 2017    Payment2018    Payment2019    Payment2020

1     Loan1      1        1        1              90             30             30             30
2     Loan1      1        1        0              80             20             40             20  
3     Loan1      1        0        1              60             40             10             10
4     Loan1      1        0        0              120            60             30             30
5     Loan2     ...       ...      ...            ...            ...            ...             ...

So For each ID (essentially the data sorted by Mapping, Asset, Fixed and then Performing) I'm looking to build a profile for the Payment Scheme.

The Payment Vector for the first ID looks like this:

PaymentVector1    PaymentVector2    PaymentVector3    PaymentVector4
1                 0.33              0.33              0.33

It is represented by the formula

PaymentVector(I)=Payment(I)/Payment(1)

The above is fine to create in an array, example code can be given if you wish.

Next, under the assumption that every payment made is replaced i.e. when 30 is paid in 2018, it must be replaced, and so on.

I'm looking to make a profile that shows the outflows (and for illustration, but not required in code, in brackets inflows) for the movement of the payments as such - For ID=1:

    Payment2017    Payment2018    Payment2019    Payment2020

17         (+90)            -30            -30           -30

18           N/A          (+30)            -10           -10

19          N/A           N/A              (+40)         -13.3

20         N/A             N/A             N/A          (+53.3)

so if you're looking forwards, the rows can be thought of what year it is and the columns representing what years are coming up.

Hence, in year 2019, looking at what is to be paid in 2017 and 2018 is N/A because those payments are in the past / cannot be paid now.

As for in year 2018, looking at what has to be paid in 2019, you have to pay one-third of the money you have now, so -10.

I've been working to turn this dataset row by row into the array but there surely has to be a quicker way using an array:

The Code I've used so far looks like:

Data Want;
Set Have;

Array Vintage(2017:2020) Vintage2017-Vintage2020;
Array PaymentSchedule(2017:2020) PaymentSchedule2017-PaymentSchedule2020;
Array PaymentVector(2017:2020) PaymentVector2017-PaymentVector2020;
Array PaymentVolume(2017:2020) PaymentVolume2017-PaymentVolume2020;

do i=1 to 4;

PaymentVector(i)=PaymentSchedule(i)/PaymentSchedule(1);

end;

I'll add code tomorrow... but the code doesn't work regardless.

So that somebody may be able to help you, can you pose a question at the end? Also it may be good to note where is the error happening, what error are you receiving, — blamb, Mar 07 '18 at 18:32
Hi sorry, my question is that I'm looking to create an array representing the payment inflows and outflows based on the second larger table with the -integer and (+integer). the error I get is that it falls outside the array regardless of setting the upper bound and lower bound — 78282219, Mar 08 '18 at 06:40

Richard · Accepted Answer · 2018-10-17T18:08:06.327

data have;
input
ID    Mapping $  Asset    Fixed    Performing    Payment2017    Payment2018    Payment2019    Payment2020; datalines;
1     Loan1      1        1        1              90             30             30             30
2     Loan1      1        1        0              80             20             40             20  
3     Loan1      1        0        1              60             40             10             10
4     Loan1      1        0        0              120            60             30             30

data want(keep=id payment: fraction:);
  set have;
  array p payment:;
  array fraction(4); * track constant fraction determined at start of profile;
  array out(4); * track outlay for ith iteration;

  * compute constant (over iterations) fraction for row;
  do i = dim(p) to 1 by -1;
    fraction(i) = p(i) / p(1);
  end;

  * reset to missing to allow for sum statement, which is <variable> + <expression>;
  call missing(of out(*));

  out(1) = p(1);
  do iter = 1 to 4;
    p(iter) = out(iter);
    do i = iter+1 to dim(p);
      p(i) = -fraction(i) * p(iter); 
      out(i) + (-p(i));  * <--- compute next iteration outlay with ye olde sum statement ;
    end;
    output;
    p(iter) = .;
  end;

  format fract: best4. payment: 7.2;
run;

I seem to get this inherent issue with my SAS where I can never create an array due to the dimensions... — 78282219, Mar 08 '18 at 06:38
74 call missing(of out(*)); 75 76 out(1) = p(1); 77 do iter = 1 to 4; 78 p(iter) = out(iter); ERROR: Array subscript out of range at line 78 column 5. — 78282219, Mar 08 '18 at 06:39
I've got it working, it was just a simple column name error. — 78282219, Mar 08 '18 at 06:45
That is a fine piece of code, thank you. I appreciate the annotation, I'm going to try to use that to learn to adapt the subsequent arrays to what i'm trying to do — 78282219, Mar 08 '18 at 06:46

score 1 · Answer 2 · answered Mar 07 '18 at 17:40

You've indexed your arrays with 2017:2020 but then try and use them using the 1 to 4 index. That won't work, you need to be consistent.

Array PaymentSchedule(2017:2020) PaymentSchedule2017-PaymentSchedule2020;
Array PaymentVector(2017:2020) PaymentVector2017-PaymentVector2020;


do i=2017 to 2020;

PaymentVector(i)=PaymentSchedule(i)/PaymentSchedule(2017);

end;

Working with an upper-triangular array in SAS (challenge +2 Points)

2 Answers2