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;
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.
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;
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
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.
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;
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
Yes HaiKuo, the Dif was not calculating all the time unless it is first and unconditional.
Paul
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.