How to calculate the lag function in SAS ?

[Rhino84]

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.

[Kurt_Bremser]

You have been around here for a while, so do this:

• edit your post to give it a descriptive subject line
• supply example data in usable form (data step with datalines)
• show the expected result

[ballardw]

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.

[Reeza]

Have you tried the lag function?

See the documentation examples:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/n0l66p5oqex1f2n1quuopdvtcjqb.h...

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.

---

[SASKiwi]

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.