topic Re: Subtracting a field value in current record from previous record in SAS Programming

Subtracting a field value in current record from previous record

Paul_NYS — Wed, 23 Jan 2013 20:39:30 GMT

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;

Re: Subtracting a field value in current record from previous record

Linlin — Wed, 23 Jan 2013 20:44:53 GMT

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;

Re: Subtracting a field value in current record from previous record

Paul_NYS — Wed, 23 Jan 2013 20:56:43 GMT

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

Re: Subtracting a field value in current record from previous record

Linlin — Wed, 23 Jan 2013 20:59:37 GMT

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.

Re: Subtracting a field value in current record from previous record

Paul_NYS — Wed, 23 Jan 2013 21:46:42 GMT

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;

Re: Subtracting a field value in current record from previous record

Haikuo — Thu, 24 Jan 2013 14:30:28 GMT

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

Re: Subtracting a field value in current record from previous record

Paul_NYS — Thu, 24 Jan 2013 14:42:47 GMT

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

Paul