My goal is to calculate the final end dollar amt, the math logic is the new end dollar amt = minimum(beginning_balance_amt,net_dollar_balance), beginning balance amount = previous end balance amount
For example, I have two snowflake tables as follow:
Table A
| Category | Beginning Dollar AMT | End Dollar AMT | Month |
|---|---|---|---|
| A | 1000 | 1000 | 2021-01-31 |
| B | 500 | 500 | 2023-02-28 |
Table B
| Category | Net Dollar balance | Month |
|---|---|---|
| A | 800 | 2021-02-28 |
| A | 2000 | 2021-03-31 |
| A | 300 | 2021-04-30 |
| B | 0 | 2023-03-31 |
| B | 200 | 2023-04-30 |
My expected output table is: Table C
| Category | Beginning Dollar AMT | End Dollar AMT | Month |
|---|---|---|---|
| A | 1000 | 800 | 2021-02-28 |
| A | 800 | 800 | 2021-03-31 |
| A | 800 | 300 | 2021-04-30 |
| B | 500 | 0 | 2023-03-31 |
| B | 0 | 0 | 2023-04-30 |
The challenge for me is I have to code it in SAS with snowflake tables, and I am not sure how to do this in SAS or snowflake.