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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.