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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.