SAS array/loop iteration variable

Question

This is my 1st time asking a question in this forum. I've used SAS/Proc SQL for about 4 years, but I'm not a code-Jedi so please give detailed answers, and am fairly new to arrays, so forgive if my question is poorly detailed/explained. I am 100% comfortable building/using simple arrays in general. But I have a very specific challenge that I cannot figure out... Hard to put into words, so this may get long winded but here goes...

For basic understanding of what I am trying to accomplish, it's somewhat similar to a simple loan amortization where month 1 the balance is the original loan amount, let's say $10,000, month 2 the balance is orig_ln_amt minus any new principal/interest payment minus any additional payment resulting in lets say $9500, month 3 results in $9000 etc.... Easy for 1 account, but I'm building an array that in effect gives the forecasted total remaining balance when all active accounts are rolled together for each month into the future, so I'm using an array that changes in size each month based on the age of the accounts.

Here's some sample code that I hoped would work:

DATA SAMPLE;
  SET INPUT_DATA; 'HAS EACH OF THE 3 INPUT ARRAYS LAID OUT SIDE BY SIDE BY SIDE
  ARRAY_ONE {193} ARRAY_ONE1-ARRAY_ONE193; 
  ARRAY_TWO {97} ARRAY_TWO1-ARRAY_TWO97;
  ARRAY_THREE {97} ARRAY_THREE1-ARRAY_THREE97; 
  OUTPUT_ARRAY {193} OUTPUT_ARRAY1-OUTPUT_ARRAY193; 'PORTFOLIO BALANCE EACH FUTURE MONTH
  DO I = 1 TO 193;
    OUTPUT_ARRAY[I] = sum(of ARRAY_ONE[I]-ARRAY_ONE193) - sum(of ARRAY_TWO[I]-ARRAY_TWO97) - sum(of ARRAY_THREE[I]-ARRAY_THREE97);
  END;
RUN;

The problem is SAS doesn't like the [I[ iteration reference inside the array calculation. I've also tried &I based on a solution another online user received in what seemed to be a similar problem. The logic makes sense and seems like it should work in theory, but doesn't...

So we've had to manually manipulate and code each of the 193 calculations:

OUTPUT_ARRAY1 = sum(of ARRAY_ONE1-ARRAY_ONE193) - sum(of ARRAY_TWO1-ARRAY_TWO97) - sum(of ARRAY_THREE1-ARRAY_THREE97);
OUTPUT_ARRAY2 = sum(of ARRAY_ONE2-ARRAY_ONE193) - sum(of ARRAY_TWO2-ARRAY_TWO97) - sum(of ARRAY_THREE2-ARRAY_THREE97);
  ...
OUTPUT_ARRAY97 = sum(of ARRAY_ONE97-ARRAY_ONE193) - sum(of ARRAY_TWO97-ARRAY_TWO97) - sum(of ARRAY_THREE97-ARRAY_THREE97);
OUTPUT_ARRAY98 = sum(of ARRAY_ONE98-ARRAY_ONE193);
  ...
OUTPUT_ARRAY193 = sum(of ARRAY_ONE193-ARRAY_ONE193);

Seems like there should be an easy solution, but we can't figure it out. Thanks in advance for any help you all can give.

Separate from answering your question: I would strongly encourage you to reformulate your data structure. SAS (and any other non-matrix language) will do Far better if you have one row per time period here. — Joe
– Joe, Commented Jun 20, 2016 at 16:38
What is contained in the arrays? Balances? Interest? Different months? Why different lengths? Please post a snippet of data. I see an aggregate SQL query solution. — Parfait
– Parfait, Commented Jun 20, 2016 at 16:43
Good questions... A little tough to answer because my example of loans amort isn't a perfect comparison and it gets a little muddy in the details. Output = Remaining Balance (goes out 193 mos); Array1 = Gross Loss $ (193mos); Array2 = Payoffs (97mos); Array3 = Regular Payments (97mos). The arrays are different lengths because there are other events that happen over the life of the portfolio... — CraigD
– CraigD, Commented Jun 20, 2016 at 17:47

Tom · Accepted Answer · 2016-06-20 18:59:13Z

2

So you have three sets of variables with adjustments to principle. You have a starting balance and you want to calculate the various intermediate balances by applying the adjustments.

First simplify your problem by making the arrays the same size. The new variables will be missing. If you actually have those variables but just don't want to include them in the calculations then drop them from the input dataset. Note that if you have variables named ONE1 to ONE193 then you can make an array named ONE that uses those variables just by using this statement array one(193);.

Second simplify your arithmetic to more closely match your problem description. It looks to me like the algorithm is that the next balance is based on the previous balance minus all of the adjustments. Include zero to handle the case when there are missing values for all of the adjustments.

%let n=193;
data want;
  set have;
  array one (&n);
  array two (&n);
  array three (&n);
  array balance Initial_balance balance1-balance&n;
  do i=1 to dim(balance)-1;
    balance(i+1)=balance(i)-sum(0,one(i),two(i),three(i));
  end;
run;

edited Jun 20, 2016 at 18:59

answered Jun 20, 2016 at 18:52

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CraigD Over a year ago

Thank you Tom. This is almost exactly what I came up with, and the results tie back to the old code... Appreciate your help very much!

Tom · Accepted Answer · 2016-06-20 17:43:06Z

1

Variable lists like VAR1-VAR20 are calculated when the data step is compiled. Your attempt to use ARRAY[I]-variable193 as a variable list is not going to fly. Just use another DO loop.

DO I = 1 TO dim(output_array);
  output_array(i)=0;
  DO J=I to dim(array_one);
    output_array(i)= sum(output_array(i),array_one(j));
  END;
  DO J=I to dim(array_two);
    output_array(i)= sum(output_array(i),array_two(j));
  END;
  DO J=I to dim(array_three);
    output_array(i)= sum(output_array(i),array_three(j));
  END;
END;

edited Jun 20, 2016 at 17:43

answered Jun 20, 2016 at 17:18

Tom

52.3k2 gold badges19 silver badges36 bronze badges

7 Comments

Joe Over a year ago

You may still want to check dims of array two/three since they're shorter than array one.

Tom Over a year ago

So separate do loops for each input array.

CraigD Over a year ago

Can't get this double loop logic to work, but I think I may have figured it out.

CraigD Over a year ago

output_array1= &begin_bal; do i = 2 to 85; output_array[I]= output_array[I-1] - ((sum (of array_one[I-1]))+ (sum (of array_two[I-1]))+ (sum (of array_three[I-1]))); do i = 86 to 97; output_array[I]= output_array[I-1] - ((sum (of array_one[I-1]))+ (sum (of array_two[I-1]))); do i = 98 to 193; output_array[I]= output_array[I-1] - ((sum (of array_one[I-1]));

CraigD Over a year ago

The array size changes each month due to regular aging, and the formula changes at points 86 and 98 because underlying data. Sorry, I know my example wasn't exact but I thought it would point everyone in the right direction...

|

Collectives™ on Stack Overflow

SAS array/loop iteration variable

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related