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

SASTad — Mon, 29 Aug 2022 00:21:53 GMT

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!

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

Shmuel — Tue, 30 Aug 2022 07:48:36 GMT

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;

topic Re: How do I Loop through rows to calculate lag values until last.ID in SAS Programming

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

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