BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MikeFranz
Quartz | Level 8

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!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Diamond | Level 26 RW9
Diamond | Level 26

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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