Subtracting a field value in current record from previous record

[Paul_NYS]

Hi

I have a data set with a date field as one of the fields in it. I was wondering if anyone knows of a way to take the date value in the present record and subtract from it the same date field in the preceding record?

As the below example indicates, I know the sum function can be used to aggregate numbers from a previous record to the current one, but am not aware of something that performs subtraction.

Paul

data s1AgeYear1Cum;

set s1AgeYear1;

by startyear exit agecat4 exitMonthCategory;

if first.agecat4 then CumulativeNumber=0;

CumulativeNumber + COUNT;

run;

[Linlin]

is this what you want?

data have;

input date mmddyy10.;

format date mmddyy10.;

cards;

01/20/2013

01/28/2013

02/20/2013

;

data want;

set have;

diff=dif(date);

proc print;run;

[Paul_NYS]

Yes Linlin, that would work fine thanks, except for one item--how do it re-set the Dif when I encounter a different grouping of records within the data set? In other words, if I have the record set sorted by child IDs and I want the Dif to perform the calculation within each set of child IDs records, how do I reset it when I encounter a new set of child records?

Paul

[Linlin · Accepted solution]

Hi Paul,

the modified code:

proc sort data=have;

by id date;

data want;

set have;

by id;

diff=ifn(first.id,.,dif(date));

run;

Good luck!

Modified after Haikuo and Paul's comments.

[Paul_NYS]

Thanks Linlin. I used a variation of this and it works fine.

Paul

data permHearingsNonFreedt1;

set sasf.permHearingsNonFreed;

by entity_id permHearDate;

diff=dif(permHearDate);

if first.entity_id then diff=.;

run;

[Haikuo]

LinLin,

The reason that Paul's variation works, while yours does not is that you have used dif() CONDITIONALLY. Dif() behaves just like lag(). Please see following two examples:

data have;

  input id date;

  cards;

  1 1

  1 3

  2 4

  2 1

  ;

  data want_1;

  set have;

by id;

if first.id then diff=.;

else diff=dif(date);

run;

data want_2;

  set have;

  by id;

diff=ifn(first.id,.,dif(date));

run;

The second one works only because it calls for dif() unconditionally, but apply the outcome with a condition. This thing bit me numerous times.

Regards,

Haikuo

[Paul_NYS]

Yes HaiKuo, the Dif was not calculating all the time unless it is first and unconditional.

Paul