Just as in life, SAS is good at "knowing the past" (lag function for previous record(s)), but not as good as "seeing the future" (reading ahead to the next row and doing something with the current row). For your issue, the lag function (or retain of a temporary variable to which you assign the fill_dt) will work fine. With that in mind, below is a design pattern I often use, especially when I need to "look ahead" to the next row(s) and do something with the current row. It takes advantage of a surrogate key (record number) + SQL's Cartesian product to either "look behind" or "look ahead" to previous record(s) or next record(s). This approach assumes the source data is already sorted in keys (id) and chronological (fill_dt) order. As @ballardw says, you don't make clear why the duplicate (2nd) row for id=3000 constitutes a "first fill"? How can you have two "firsts" in your id group? Anyway, with the "have" and "want" you provided, this gives your desired result. Whether you choose to use this approach, IMO this is a handy design pattern to have in your kit bag, and I encourage you to understand how this works. HTH... data have;
input id $4. fill_dt:mmddyy10.;
format fill_dt MMDDYY10.;
datalines;
1000 08/21/2018
2000 02/02/2018
2000 02/17/2018
2000 11/07/2018
2000 11/12/2018
2000 11/28/2018
3000 03/05/2018
3000 03/05/2018
3000 05/29/2018
3000 10/24/2018
3000 11/24/2018
;
run;
* create surrogate key ;
data sk / view=sk;
sk+1;
set have;
run;
* use SQL + Cartesian product to look behind (or ahead) ;
proc sql;
create table test as
select
a.id
,b.fill_dt as prev_fill_dt
,a.fill_dt as curr_fill_dt
from
sk a
left join
sk b
on
a.id = b.id
and
a.sk = b.sk+1
;
quit;
* final approach ;
proc sql;
create table want as
select
a.id
,b.fill_dt as prev_fill_dt
,a.fill_dt as curr_fill_dt
,intck('day',b.fill_dt,curr_fill_dt) as days_difference
from
sk a
left join
sk b
on
a.id = b.id
and
a.sk-1 = b.sk
having
/* first record in the id group */
missing(prev_fill_dt)
or
/* duplicate record - not sure why the 2nd record is still the "First Fill Day"? */
intck('day',prev_fill_dt,curr_fill_dt) = 0
or
/* # of days difference between the previous and current record > 120 */
intck('day',prev_fill_dt,curr_fill_dt) > 120
;
quit; P.S.: I think your post would be a bit clearer if you said "difference between current fill date and previous fill dt" instead of "difference between current fill date and last fill dt". When I first read this, I thought "last fill dt" was the last fill dt (last record) in the id group.
... View more
