DATA Step, Macro, Functions and more

Subtracting a field value in current record from previous record

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Subtracting a field value in current record from previous record

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;


Accepted Solutions
Solution
‎01-23-2013 03:59 PM
Super Contributor
Posts: 1,636

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


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.

View solution in original post


All Replies
Super Contributor
Posts: 1,636

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

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;

Regular Contributor
Posts: 216

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

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

Solution
‎01-23-2013 03:59 PM
Super Contributor
Posts: 1,636

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


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.

Regular Contributor
Posts: 216

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

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;

Respected Advisor
Posts: 3,156

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

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

Regular Contributor
Posts: 216

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

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

Paul

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1622 views
  • 0 likes
  • 3 in conversation