How do I create a loop that keeps on subtracting a value?

[DeepaG]

Hi All

I am using SAS EG 8.2.

Please help! How can I create the attached excel sheet calculation in SAS?

At each change of the number & at the end of the month I want to build in a line that says "INTEREST" in the tran_type column with the calculation under "AMOUNT1".

The calculation for "AMOUNT1" will always reference the first line of that account that reads "INTEREST" at the top subtract the number in amount1 & also subtracting the unearned_bal that I get from another table.

Now when the next month arrives, the calculation must do the same but instead subtract the new unearned_bal for the month from the first line

[Tom]

Spreadsheets are dangerous to download.  Can you share your INPUT dataset as a data step instead?  Then do the same to demonstrate what dataset you want to create from that input.

Without seeing your data it is hard to figure out what you are talking about.

If you are starting with one observation and generating many then add an OUTPUT statement inside the loop.  So perhaps you start with the current value and want to see what would happen if you compounded interest over 12 months?  So this loop will turn each observation into 13 observations.  The current one and then 12 follow up months with balance growing each month.

data want;
  set have;
  do month=0 to 12;
     output;
     balance + (balance * interest_rate);
  end;
run;
     

If you already have multiple observations and the value you need appears on an earlier observation (but for some reason its value has changed by the time you get to the observation where you want to use the older value) then you probably want to use a NEW variable so that you can RETAIN its value so that it is available to use in calculations on later observations.  You will need to have some way of knowing when this value should be reset.  Perhaps when you move to a new account?

An easy example to demonstrate is a change from baseline calculation.  Say you have a dataset for multiple IDs that is sorted by ID.  And for each observation you want to create a new variable with the difference between the current VALUE and the first VALUE.  Then you could use BY group processing and make TWO new variables. One to retain the BASELINE value. The other to get the change.

data want;
  set have;
   by id;
  if firat.id then baseline=value;
  retain baseline;
  change = value - baseline;
run;

[ballardw]

Note that while spreadsheets have a lot of problems to begin with as @Tom mentions, when they contain PICTURES they are completely useless as we can't even see which are numeric values or character, what might be calculated, and in the case of conditional behavior like your inserting a line can't see what conditions might be used in the spread sheet.

As a minimum provide raw values as text, preferably pasted into a text box opened with the </> icon. If you are going to show pictures you can use the camera icon to insert pictures that appear in line in the message.

Your example shows "change of number" and "end of month" that do not include your inserted line. So you need to clearly state why or why not that occurs. Number = 2314185036 and Trans_dte=31Mar2025 for example.

The behavior I think you are requesting may be possible with Proc Report once you  have a SAS data set. Maybe. Need more details of the data and the rules involved. For example looking at that example I am not sure what you are subtracting. Where is the "table" that you get the "unearned bal" from? That would be a critical part of ever accomplishing this. Linking values from that table to the data used for this is likely to be a non-trivial exercise.

Your statement "Now when the next month arrives, the calculation must do the same but instead subtract the new unearned_bal for the month from the first line." brings up question like how does the "next month arrive"? Is it a separate file, what type? This is going to lead to data management questions like creating data sets (warning: Spreadsheets cause half or more of the data related questions on this forum), combining the data sets, selecting records. And does the table with the "unearned_bal" also change?

[DeepaG]

Hi. Many thanks for your prompt replies.

I've provided my input data below. Please forgive me if the code may be incorrect - I'm unsure of syntax.

I've also brought in the unearned bal column here.

DATA WORK1;
INPUT NUMBER TRANS_DTE DATE9. AMOUNT1 AMOUNT2 TRAN_TYPE $ UNEARNED_BAL;
CARDS;
23141850537	19DEC2024	1907605.32	5013.87	INTEREST	0.00
23141850537	19DEC2024	6037.00	6037	INIT FEE		0.00
23141850537	19DEC2024	1400000.00	1400000	DISBURSEMENTS	0.00
23141850537	27DEC2024	-12234.38	-12234.38	PAYMENTS	0.00
23141850537	25JAN2025	-12234.38	-12234.38	PAYMENTS	0.00
23141850537	31JAN2025	-57412.68	-57412.68	FIN CHRG ADJ	1833271.43
23141850537	25FEB2025	-12022.04	-12022.04	PAYMENTS	1822777.27
23141850537	25MAR2025	-12022.04	-12022.04	PAYMENTS	1724443.45	
29236400001	05DEC2024	1787882.32	9708.1	        INTEREST	0.00
29236400001	05DEC2024	6037.00       	6037.00	        INIT FEE	0.00
29236400001	05DEC2024	1300000.00	1300000	   DISBURSEMENTS	0.00	
29236400001	30DEC2024	-11427.71	-11427.71	PAYMENTS	0.00
29236400001	30JAN2025	-11427.71	-11427.71	PAYMENTS	0.00	
29236400001	31JAN2025	-53706.06	-53706.06	FIN CHRG ADJ	1713349.61	
29236400001	28FEB2025	-11229.08	-11229.08	PAYMENTS	1703550.89	
;RUN;

What I want to do is the below:

DATA WORK2;
INPUT NUMBER TRANS_DTE DATE9. AMOUNT1 AMOUNT2 TRAN_TYPE $ UNEARNED_BAL;
CARDS;
23141850537	19DEC2024	1907605.32	5013.87	        INTEREST	0.00	
23141850537	19DEC2024	   6037.00	 6037.00	INIT FEE	0.00	
23141850537	19DEC2024	1400000.00	1400000	   DISBURSEMENTS	0.00	
23141850537	27DEC2024	-12234.38	-12234.38	PAYMENTS	0.00	
23141850537	25JAN2025	-12234.38	-12234.38	PAYMENTS	0.00	
23141850537	31JAN2025	-57412.68	-57412.68	FIN CHRG ADJ	1833271.43	
23141850537     31JAN2025	     0.00        69320.02	INTEREST	0.00
23141850537	25FEB2025	-12022.04	-12022.04	PAYMENTS	1822777.27	
23141850537	28FEB2025	     0.00	 10494.16	INTEREST	0.00	
23141850537	25MAR2025	-12022.04	-12022.04	PAYMENTS	1724443.45	
23141850537	31MAR2025	     0.00	 98333.82	INTEREST	0.00	
29236400001	05DEC2024      1787882.32	  9708.10	INTEREST	0.00
29236400001	05DEC2024	  6037.00	  6037.00	INIT FEE	0.00	
29236400001	05DEC2024      1300000.00      1300000.00	DISBURSEMENTS	0.00	
29236400001	30DEC2024	-11427.71	-11427.71	PAYMENTS	0.00	
29236400001	30JAN2025	-11427.71	-11427.71	PAYMENTS	0.00	
29236400001	31JAN2025	-53706.06	-53706.06	FIN CHRG ADJ	1713349.61
29236400001	31JAN2025        0.00            64824.61       INTEREST	0.00
29236400001	28FEB2025	-11229.08	-11229.08	PAYMENTS	1703550.89
29236400001	28FEB2025	     0.00	  9798.72	INTEREST	0.00
;RUN;

Beginning from Jan2025, I want to create the lines in bold (per number).

The calculation in the AMOUNT2 column is as follows:

AMOUNT2 = AMOUNT1 - AMOUNT2 (first line for this number where tran_type is "INTEREST") - UNEARNED BAL for Jan2025

               = 1907605.32 - 5013.87 - 1833271.43

               = 69320.02

The new month or next month 'arrives' when the dataset is updated. It is updated daily via a scheduled run. It is not a separate file. When the datset is updated and reaches the end of a month for example, 28 February 2025 then I want to create another line per number - calculation below:

AMOUNT2 = AMOUNT1 - AMOUNT2 (first line for this number where tran_type is "INTEREST") - UNEARNED BAL for Feb2025 - INTEREST line calculated for Jan2025

                   = 1907605.32 - 5013.87 - 1822777.27 - 69320.02

                   = 10494.16

When the dataset is updated and reaches to 31 March 2025, I want to create another line per number - calculation below:

AMOUNT2 = AMOUNT1 - AMOUNT2 (first line for this number where tran_type is "INTEREST") - UNEARNED BAL for Mar2025 - INTEREST line calculated for Jan2025 -  INTEREST line calculated for Feb2025

                  = 1907605.32 - 5013.87 - 1724443.45 - 69320.02 - 10494.16

                  = 98333.82

The same must happen for the months hereafter when the dataset is updated.

The second number is just another similar example.

Hoping this explanation makes a bit more sense.

Thanking you in advance.

[Tom]

Don't use tabs in in-line data (in fact don't use tabs in program files at all) they just make the data look funny and make it hard to read.  For example your second line of data had an extra tab which moved the last value into a field your input statement did not read.  And in some places you had a lot of actual spaces.

Make sure to read delimited data using LIST MODE and not formatted mode to prevent the INPUT statement from reading past the delimiters by prefixing any informats specified in the INPUT statement with the colon modifier.  Make sure to attach formats to variables that NEED them, like DATE values.

data have; 
  infile cards dsd truncover ; 
  INPUT NUMBER TRANS_DTE :DATE9. AMOUNT1 AMOUNT2 TRAN_TYPE :$20. UNEARNED_BAL; 
  format trans_dte date9.; 
CARDS; 
1,19DEC2024,1907605.32,5013.87,INTEREST,0.00 
1,19DEC2024,6037.00,6037,INIT FEE,0.00 
1,19DEC2024,1400000.00,1400000,DISBURSEMENTS,0.00 
1,27DEC2024,-12234.38,-12234.38,PAYMENTS,0.00 
1,25JAN2025,-12234.38,-12234.38,PAYMENTS,0.00 
1,31JAN2025,-57412.68,-57412.68,FIN CHRG ADJ,1833271.43 
1,25FEB2025,-12022.04,-12022.04,PAYMENTS,1822777.27 
1,25MAR2025,-12022.04,-12022.04,PAYMENTS,1724443.45 
2,05DEC2024,1787882.32,9708.1,INTEREST,0.00 
2,05DEC2024,6037.00,6037.00,INIT FEE,0.00 
2,05DEC2024,1300000.00,1300000,DISBURSEMENTS,0.00 
2,30DEC2024,-11427.71,-11427.71,PAYMENTS,0.00 
2,30JAN2025,-11427.71,-11427.71,PAYMENTS,0.00 
2,31JAN2025,-53706.06,-53706.06,FIN CHRG ADJ,1713349.61 
2,28FEB2025,-11229.08,-11229.08,PAYMENTS,1703550.89 
;

You did not explain which observation you are using to base adding the INTEREST line.  Let's assume it is the first observations for a MONTH.  It is always going to be easier to look back to the past than predict the future so I kind of sounds like your algorithm for calculating instead is something like this:

data interest ;
  set have;
  by number trans_dte ;
  if (intnx('month',trans_dte,0) ne intnx('month',lag(trans_dte),0))
    or first.number then do;
     amount2 = amount1 - amount2 - UNEARNED_BAL;
     amount1 =0;
     UNEARNED_BAL =0;
     TRAN_TYPE = 'INTEREST';
     trans_dte = intnx('month',trans_dte,-1,'e');
     output;
  end;
run;

Which yields these results for your HAVE dataset above.

I am not sure I understand the logic of changing a file that some other process is already changing.  That just sounds like something that would be very hard to maintain.  Perhaps you could pass these new interest records off to that process to incorporate into its actual DATABASE instead?

[FreelanceReinh]

Just a minor side note: When you do arithmetic with money amounts with (typically) two decimal places, rounding errors can easily occur that you might not expect. I would use the ROUND function to rectify those errors.

Example:

655   data test;
656   amount1=2.20;
657   amount2=1.20;
658   unearned_bal=1;
659   crude = amount1 - amount2 - unearned_bal;
660   if crude ne 0 then put 'Surprised?' +1 crude;
661   amount2 = round(amount1 - amount2 - unearned_bal, 1e-7);
662   if amount2=0 then put 'OK!';
663   run;

Surprised? 2.220446E-16
OK!

If you know that none of your amounts and balances has more than two decimals (i.e., involves fractions of a cent), using 0.01 as the rounding unit is even safer than the suggested 1e-7.