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) |
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?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.