topic Calculate difference of staggered rows in SAS Programming

Calculate difference of staggered rows

TheNovice — Mon, 19 Oct 2020 03:57:18 GMT

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

Re: Calculate difference of staggered rows

andreas_lds — Mon, 19 Oct 2020 05:09:50 GMT

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.

Re: Calculate difference of staggered rows

mkeintz — Mon, 19 Oct 2020 05:12:36 GMT

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.

Re: Calculate difference of staggered rows

TheNovice — Mon, 16 Nov 2020 13:54:22 GMT

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!