LAG function

[MikeFranz]

Hi, I've been trying to do something fairly simple for a couple of hours, but am struggling.

I've read that using LAG function can lead to some unexpected results, but I'm not sure what I'm doing that could be causing the issue.

I am trying to create the dataset below:

Day End_Reason                Prev_End_Reason Last_Day
1    Reason 1                  ""                0
2    Reason 1                  ""                0
3    Reason 1                  ""                1
4    Reason 2                  Reason 1          0
5    Reason 2                  Reason 1          0
6    Reason 2                  Reason 1          1
7    Reason 2                  Reason 2          1
8    Reason 2                  Reason 2          0
9    Reason 2                  Reason 2          0
10   Reason 2                  Reason 2          1
11   Reason 3                  Reason 2          0
12   Reason 3                  Reason 2          0
13   Reason 3                  Reason 2          0
14   Reason 3                  Reason 2          1
15   ""                        Reason 3          0

So, each row has an "End_Reason". If the previous row has Last_Day =1  would like the following observation to be have "Previous_End_Reason" = "End_Reason" from the previous row.

I currently have the above, excluding the "Prev_end_reason" variable, which I am trying to attain.

My code currently looks like this:

data want;
	set have;	
	if lag(last_day) = 1 then prev_end_reason = lag(end_reason);
RUN;

For some reason, the column "Prev_End_Reason" remains blank throughout. If I use the following code:

data want;
	set have;	
	if lag(last_day) = 1 then prev_end_reason = 10;
RUN;

It seems to work, putting a "10" in the correct places, so I can only assume there is something wrong with the bit "lag(end_reason)", but I can't work out what?

Thanks!

[Kurt_Bremser]

Never use lag() in a conditional branch. Lag() sets up a FIFO chain that is only filled when lag() is actually called. Will test-run your code next time I sit in front of my SAS (max. 1 hour).

[RW9 · Accepted solution]

Yes, I only ever use lag() as a lookup function, I always use retained variables for anything else.  So I would do:

data want;
  set have;
  retain lst_day lst_reason;	
  if lst_day=1 then prev_end_reason = lst_reason;
  lst_day=last_day;
  lst_reason=prev_end_reason;
run;;

Note I haven't tested this - provide test data in the form of a datastep and what you want out. 

[MikeFranz]

Hey, thank you! That worked! Just changed the code slightly to:
data want;
set have;
retain lst_day lst_reason;
if lst_day=1 then prev_end_reason = lst_reason;
lst_day=last_day;
lst_reason=end_reason;
run;
Many thanks!

Quick question, how would you do this if you wanted to "lag", say, 10 rows?

[RW9]

In what sense sorry.  Do you mean you want to hold the value from 10 rows before, or all the rows up to 10?  To do the first is pretty straight forward, just add a counter, the second is a bit more coding, have an array and retain that:

array l_reason{10};
retain l_reason:;

Then keep an index on that.  Supplying test data (in a datastep) and required output really shows a problem simply!