DATA Step, Macro, Functions and more

LAG function

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

LAG function

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!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎11-27-2017 04:16 AM
Super User
Super User
Posts: 9,840

Re: LAG function

Posted in reply to MikeFranz

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. 

View solution in original post


All Replies
Super User
Posts: 10,574

Re: LAG function

Posted in reply to MikeFranz

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎11-27-2017 04:16 AM
Super User
Super User
Posts: 9,840

Re: LAG function

Posted in reply to MikeFranz

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. 

Contributor
Posts: 47

Re: LAG function

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?
Super User
Super User
Posts: 9,840

Re: LAG function

Posted in reply to MikeFranz

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 196 views
  • 0 likes
  • 3 in conversation