Fluorite | Level 6

## How do I Loop through rows to calculate lag values until last.ID

I need to loop through rows of a table and create new variables simultaneously until last.AccountID. Here is an example:

Existing table - Fees

AccountID    ReportDate         Old_Interest_Amt            Balance.             Total_Fees

123               07Mar2022.            34.63                              2282                          39

123               07Apr2022              18.53                              2281                          78

456               11May2022              25.7                               1337                          44

456                10Jun2022              26.94                             1411                          88

456                 11Jul2022              11.21                               1464                       132

For each row until last.AccountID, I'd like to create three new variables - I've listed their formulae below

New_Balance = Balance - Total_Fees

New_Int_Amt = (0.099 * New_Balance)/12

Diff = Old_Interest_Amt - New_Int_Amt

Once I create above three variables on the first row, I move to the second row and subtract the value under Diff column from the balance and create above three variables again.  I then use the diff value from the second row and subtract it from the balance from the third row and create those three variables again. I need to repeat this process until last.AccountID.

For example, lets consider AccountID 456 - I calculate those three variables on the first row where I get the diff value of 15. I remove 15 from the balance from the second row and calculate those three variables again and this time I get a diff value of 16. I remove 16 from the balance from the third row and create the same variables. I repeat this process till I reach the last transaction for that particular account.

I've included the code I've tried below where I'm unable to iterate through till the last transaction.

# I compute those three variables for each account on their first row

Data have;

set fees;

by AccountID;

if first.AccountID then do;

New_Balance = Balance - Total_Fees;

New_Int_Amt = (0.099 * New_Balance)/12;

Diff = Old_Interest_Amt - New_Int_Amt;

end;

lag_diff = lag(Diff);

run;

# I try to remove the diff value from the balance in the subsequent rows

Data have_1;

set have;

do until(last.AccountID);

Balance = Balance - lag_diff;

New_Balance = Balance - Total_Fees;

New_Int_Amt = (0.099 * New_Balance)/12;

Diff = Old_Interest_Amt - New_Int_Amt;

lag_diff = lag(Diff);

end;

run;

Output I want to see :

AccountID           Old_Interest_Amt       Balance.             Total_Fees.      New_Balance      New_Int_Amt.       Diff

123                          34                            2282                       39                    2243                      18                   16

123                          18                            2265                       78                    2187                      18                   0

456                          25                            1337                       44                    1293                      10                    15

456                          26                            1396                        88                    1308                     10                     16

456                          11                             1448                     132                     1316                     10                    1

I'd appreciate if someone is able to correct my logic and help me see the output above. Thank you!

Garnet | Level 18

## Re: How do I Loop through rows to calculate lag values until last.ID

Adjus next code to the required number of decimal places:

``````data fees;
infile datalines truncover;
informat accountid 3.reportdate date9. old_interest_amt 5.2
balance 4. total_fees best4.;
input accountid ReportDate Old_Interest_Amt Balance Total_Fees;
format reportdate date9. old_interest_amt 5.2;
datalines;
123 07Mar2022 34.63 2282 39
123 07Apr2022 18.53 2281 78
456 11May2022 25.7  1337 44
456 10Jun2022 26.94 1411 88
456 11Jul2022 11.21 1464 132
; run;

Data want;
set fees;
by AccountID;
retain lag_diff;
if first.AccountID then do;
lag_diff=0;
New_Balance = Balance - Total_Fees;
New_Int_Amt = (0.099 * New_Balance)/12;
Diff = Old_Interest_Amt - New_Int_Amt;
end;
else do;
New_Balance = Balance - lag_diff - Total_Fees;
New_Int_Amt = (0.099 * New_Balance)/12;
Diff = Old_Interest_Amt - New_Int_Amt;
end;output;
lag_diff = diff;
format New_Balance 4. lag_diff New_Int_Amt Diff 5.2
run;

``````
Discussion stats