Unable to use two lag functions at once

[moreka]

I don't necessarily need a coding solution, but am looking to understand why this code doesn't work.

data want; set have; 

  if id=lag(id) then last_visit=lag(visit);

run;

Have:

id	visit
2	7/5/2014
2	8/30/2014
2	9/24/2014
4
8	4/25/2014
8	4/26/2014
8	5/28/2014

Want:

id	visit	last_visit_date
2	7/5/2014
2	8/30/2014	7/5/2014
2	9/24/2014	8/30/2014
4
8	4/25/2014
8	4/26/2014	4/25/2014
8	5/28/2014	4/26/2014

Actual Result:

id	visit	last_visit_date
2	7/5/2014
2	8/30/2014	7/5/2014
2	9/24/2014	8/30/2014
4
8	4/25/2014
8	4/26/2014	9/24/2014
8	5/28/2014	4/26/2014

I believe I could also accomplish this with a retain statement, but it eventually worked after outright creating the variable lag_visit (below).   I don't completely understand why it was necessary to do this.

data want; set have; 

     lag_visit=lag1(visit_dt);

     if id=lag(id) then last_visit=lag_visit;

run;

Thanks,

K

[Reeza · Accepted solution]

Lag function creates a queue. If you execute it conditionally, then it isn't updated every iteration.

In general, don't use Lag functions in conditional statements.

This paper illustrates your issue and explains it better than I can

http://www.howles.com/saspapers/CC33.pdf

[moreka]

Thanks -- exactly what I was looking for!