Calculate difference of staggered rows

[TheNovice]

Hi all,

Not sure how to go about this at all...

I have data like below: DIFF = DUE - ACTV_AMT. 

ROW	ID	ACTV_AMT	DATE	DUE	DIFF
1	12	350	09Jul2020	745.38	395.38
2	12	300	10Aug2020	778.7	478.7
3	12	300	14Aug2020	478.7	178.7
4	12	178.7	09Sep2020	571.49	392.79
5	12	400	11Sep2020	392.79	-7.21

I need to add another calculated column to get an output like:

The formula would be: Change =  Due(row2) - diff (row 1). 

Another complication: For the first row for Change, formula would be: Change = Due - 0. 

ROW	ID	ACTV_AMT	DATE	DUE	DIFF	CHANGE
1	12	350	09Jul2020	745.38	395.38	745.38
2	12	300	10Aug2020	778.7	478.7	383.32
3	12	300	14Aug2020	478.7	178.7	0
4	12	178.7	09Sep2020	571.49	392.79	392.79
5	12	400	11Sep2020	392.79	-7.21	0

Ideally a proc sql solution is needed but I will take anything to complete this ad hoc first... appreciate any direction

[andreas_lds]

Ideally you won't even think of using sql for a task that requires observations to be processed in a defined order.

Similar problems are daily posted, so using the search function will show you at least n ways to solve the problem.

Have a look at the documentation of the lag-function.

[mkeintz · Accepted solution]

I believe this untested data step would work.  I would test it if you could provide sample data in the form of a working data step.

The key element here is the lag function:

data want;
  set have;
  by id;
  change=ifn(first.id,due,due-lag(diff));
run;

I presume your data are sorted by id/date.  The program above embeds the lag function inside an IFN function.  The latter tests whether the record-in-hand is the start of an id - in which case the change variable is assigned a value equal to due.  Otherwise due is calculated as you describe.

[TheNovice]

Thanks so much Mkeintz. Apologies for the delay but was pulled off for an urgent ask on a different project. I tested it and it works!