I would argue that there really isn't a true LAG function in SAS either.
I dunno...it's listed in the doc as a function 😉 IIRC with a number of caveats - it's been a while since I read the doc on LAG and am too lazy to look it up right now.
This misleadingly-named function should really be called something like UPDFIFOQ or UFQ (for "update fifo queue").
SASWare Ballot? 😉
I just never really use LAG (LAG1 that is):
data class;
length lag_name $10;
retain lag_name;
lag_name=name;
set sashelp.class;
run;
Or perhaps:
proc sort data=sashelp.class out=class (keep=name age);
by age name;
run;
data want;
length lag_name $10;
retain lag_name;
lag_name=name;
set class;
by age name;
if first.age then call missing(lag_name);
run;
But it gets more cumbersome the more grouping columns you have.
Things get trickier using this approach if I need LAG2, LAG3, etc, but I rarely have a need for that.
And of course, the data step can actually look ahead about as easily as it can look back:
data past_present_future;
merge have
have (firstobs=2 keep=x rename=(x=next_x));
if _n_>1 then set have (keep=x rename=(x=past_x));
run;
Define "easily" lol. I'll define it as "intuitively obvious to someone with less than 3 years SAS experience" (or easily found as an example in the doc).
But you've shown a cool technique mixing merge and double set statement. I've done something similar in the past using double (or triple) set statements, and the premature eof (firstobs=2) was problematic. I can't remember what approach I used? point=? do until eof? Anyway, now I have your approach, so thanks!
Standalone example:
data past_present_future;
format past_name name next_name;
if _n_>1 then set sashelp.class (keep=name rename=(name=past_name));
merge sashelp.class
sashelp.class (firstobs=2 keep=name rename=(name=next_name));
run;
However, how would you do the below using your approach if you needed to group (without lots of first. and last. processing + call missings)?
proc sort data=sashelp.class out=class (keep=name age);
by age name;
run;
data have/view=have;
sk+1;
set class;
run;
proc sql;
create table want as
select b.name as past_name
,a.name
,c.name as next_name
,a.age
from have a
full join have b
on a.age=b.age and a.sk=b.sk+1
full join have c
on a.age=c.age and a.sk+1=c.sk
where a.name is not missing
;
quit;
Or worse:
proc sort data=sashelp.cars out=cars (keep=make type model drivetrain);
by make type drivetrain;
run;
data have/view=have;
sk+1;
set cars;
run;
proc sql;
create table want as
select b.model as past_model
,a.model
,c.model as next_model
,a.make
,a.type
,a.drivetrain
from have a
full join have b
on a.make=b.make and a.type=b.type and a.drivetrain=b.drivetrain and a.sk=b.sk+1
full join have c
on a.make=b.make and a.type=b.type and a.drivetrain=b.drivetrain and a.sk+1=c.sk
where a.model is not missing
;
quit;
(I haven't done any in depth checking on this last example to see if the results make sense - they looked ok on a quick glance).
Edit: I have used your approach, or something similar, when performance was an issue. Say 50M records, and I needed LEAD within say two grouping columns. 50M * 50M * 50M Cartesian, even when filtered out with the join criteria, can be a bit of an "issue"! But it required a bit of first. and last. processing plus call missing statements (and a lot of testing) to get the desired results.
Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Click image to register for webinar
