topic Re: Help !! in SAS Programming

How to calculate the lag function in SAS ?

Rhino84 — Thu, 13 Oct 2022 16:53:35 GMT

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.

Re: Help !!

Kurt_Bremser — Thu, 13 Oct 2022 16:45:41 GMT

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

Re: How to calculate the lag function in SAS ?

ballardw — Thu, 13 Oct 2022 16:58:43 GMT

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.

Re: How to calculate the lag function in SAS ?

Reeza — Thu, 13 Oct 2022 17:10:08 GMT

Have you tried the lag function?

See the documentation examples:

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

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.

Re: How to calculate the lag function in SAS ?

SASKiwi — Thu, 13 Oct 2022 23:54:08 GMT

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.