Please find the sample data below
business_dt | Card no | available_bal | amt_transaction | Difference |
10-May-21 | 1111 | 35 | 4 | null |
9-May-21 | 1111 | 30 | 5 | 0 |
8-May-21 | 1111 | 28 | 2 | 0 |
7-May-21 | 1111 | 14 | 4 | 0 |
6-May-21 | 1111 | 12 | 2 | 0 |
10-May-21 | 22222 | 34 | 8 | null |
9-May-21 | 22222 | 30 | 4 | 0 |
8-May-21 | 22222 | 20 | 10 | 0 |
7-May-21 | 22222 | 15 | 5 | 0 |
6-May-21 | 22222 | 10 | 5 | 0 |
The table shows transaction data of members. The logic for the table is as follows.
available_bal + amt_transaction = Available_balance
eg : transaction date 06 may 22 . for member 22222 : 10+5=15 (which carries on to next row) and any new transaction on amt_transaction is added and it will be base balance for the next amt_transaction. The difference is basically the lag function that i had written as below:
cast(available_bal + amt_transaction - lag(available_bal,1)
over (partition by business_dt,member_NO
order by member_NO,business_dt) as decimal(8,2)) difference
Please help me on how to do this in SAS. The out put is the table shown above. I need to write a script on how to do this in SAS.
You have been around here for a while, so do this:
Hint: Looks more like a RETAIN than a LAG solution for available_bal + amt_transaction = Available_balance.
Coupled with BY group processing and FIRST. to reset the accumulator at change of card no.
Have you tried the lag function?
See the documentation examples:
And a good reference on issues with using LAG().
https://support.sas.com/resources/papers/proceedings09/055-2009.pdf
@Rhino84 wrote:
Please find the sample data below
business_dt Card no available_bal amt_transaction Difference 10-May-21 1111 35 4 null 9-May-21 1111 30 5 0 8-May-21 1111 28 2 0 7-May-21 1111 14 4 0 6-May-21 1111 12 2 0 10-May-21 22222 34 8 null 9-May-21 22222 30 4 0 8-May-21 22222 20 10 0 7-May-21 22222 15 5 0 6-May-21 22222 10 5 0
The table shows transaction data of members. The logic for the table is as follows.
available_bal + amt_transaction = Available_balance
eg : transaction date 06 may 22 . for member 22222 : 10+5=15 (which carries on to next row) and any new transaction on amt_transaction is added and it will be base balance for the next amt_transaction. The difference is basically the lag function that i had written as below:
cast(available_bal + amt_transaction - lag(available_bal,1)
over (partition by business_dt,member_NO
order by member_NO,business_dt) as decimal(8,2)) difference
Please help me on how to do this in SAS. The out put is the table shown above. I need to write a script on how to do this in SAS.
If the data you wish to process is coming from an external database then you can continue to use your custom SQL in a SAS SQL Passthru query. You don't need to change the logic at all.
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.