Calculate previous accounts' balance

newboy1218 — Wed, 30 Nov 2022 19:33:46 GMT

Hi, I have a table contains customer ID, account #s, each month's balance (OS bal) of the accounts. Each customer can have multiple accounts in the dataset.

What I want is to calculate on each customer, the total outstanding balance from all accounts currently on file when a new account is open (let's call it 'Total_Customer_OSBal').

For example, a customer has 3 accounts, one opened in Jan 2022, one opened in Mar 2022, and the last one opened in Jun 2022. The 'Total_Customer_OSBal' for the 1st account when it opened in Jan 2022 is $0. The 'Total_Customer_OSBal' for the 2nd account when it opened in Mar 2022 is the OS balance from the 1st account in Mar 2022. The 'Total_Customer_OSBal' for the 3rd account when it opened in Jun 2022 is the OS balance from the 1st account in Jun 2022 and 2nd account in Jun 2022.

Is there an efficient way to do this?

Here are the example chart for illustration:

HAVE

Customer_ID	Account_num	Date_Open	Snap_Date	Balance
1	1A	MAR2022	MAR2022	500
1	1A	MAR2022	APR2022	450
1	1A	MAR2022	MAY2022	400
1	1B	MAY2022	MAY2022	600
2	2A	JAN2022	JAN2022	100
2	2A	JAN2022	FEB2022	90
2	2A	JAN2022	MAR2022	80
2	2A	JAN2022	APR2022	70
2	2A	JAN2022	MAY2022	60
2	2B	JAN2022	JAN2022	210
2	2B	JAN2022	FEB2022	190
2	2B	JAN2022	MAR2022	170
2	2B	JAN2022	APR2022	150
2	2B	JAN2022	MAY2022	130
2	2C	MAY2022	MAY2022	700

WANT

Customer_ID	Account_num	Date_Open	Snap_Date	Balance	Total_Customer_OSBal
1	1A	MAR2022	MAR2022	500	0
1	1A	MAR2022	APR2022	450	.
1	1A	MAR2022	MAY2022	400	.
1	1B	MAY2022	MAY2022	600	400
2	2A	JAN2022	JAN2022	100	.
2	2A	JAN2022	FEB2022	90	.
2	2A	JAN2022	MAR2022	80	.
2	2A	JAN2022	APR2022	70	.
2	2A	JAN2022	MAY2022	60	.
2	2B	JAN2022	JAN2022	210	100
2	2B	JAN2022	FEB2022	190	.
2	2B	JAN2022	MAR2022	170	.
2	2B	JAN2022	APR2022	150	.
2	2B	JAN2022	MAY2022	130	.
2	2C	MAY2022	MAY2022	700	190 (60+130)

Re: Calculate previous accounts' balance

HB — Mon, 05 Dec 2022 22:54:35 GMT

Your "want" makes no sense to me. Are there more decision rules? Why are there no Total_Customer_OSBal balances for customer 2 for Feb, Mar, Apr, and May?

topic Re: Calculate previous accounts' balance in SAS Programming

Calculate previous accounts' balance

Re: Calculate previous accounts' balance