topic Re: Must I reset the value of lag() when using BY variables? in SAS Programming

Must I reset the value of lag() when using BY variables?

dan_waldo — Wed, 13 Aug 2014 22:05:04 GMT

I would like to count the number of runs in a dataset, but I believe that my code has a hole in it.

I had thought to use:

proc sort data=stuff; by key id;

run;

data runs;

set stuff; by key;

retain runs;

if first.key then runs=1;

else if id ne lag(id) then runs+1;

if last.key then output;

run;

However, it appears that lag(id) is not set to missing when the BY variable key changes. Am I looking at this wrong? Is there a faster/better way to do it?

Thanks!

Re: Must I reset the value of lag() when using BY variables?

data_null__ — Wed, 13 Aug 2014 22:14:48 GMT

You don't need lag.

BY KEY ID;

if first.key then runs=0;

if first.id then runs+1;

if last.key then output;

Re: Must I reset the value of lag() when using BY variables?

stat_sas — Wed, 13 Aug 2014 22:29:55 GMT

Just need a slight change

data runs;

set stuff;

by key;

delta=id-lag(id);

if first.key then runs=1;

if not first.key then do;

if delta ne 0 then runs+1;

end;

if last.key then output;

run;

Re: Must I reset the value of lag() when using BY variables?

Patrick — Wed, 13 Aug 2014 22:48:43 GMT

Never use lag() within an if condition as it only updates the lag value when executed - so the function needs to execute in every single iteration of the data step.

and as already posted: there is no need to use lag()

Re: Must I reset the value of lag() when using BY variables?

Cynthia_sas — Thu, 14 Aug 2014 01:27:51 GMT

Hi:

Patrick is correct. Please refer to this Tech Support note that demonstrates that using lag conditionally will probably result in undesired results: 24665 - Use the LAG function to conditionally carry information down a data set

If you run the code in that note, the approach that creates variable is the approach to use. As the note indicates (in the code), the LAG function stores values on the queue only when it is called, so you must call LAG unconditionally (outside the IF condition) to get the correct lagged value. And this paper has a longer explanation of the usage http://support.sas.com/resources/papers/proceedings09/055-2009.pdf.

cynthia

Re: Must I reset the value of lag() when using BY variables?

stat_sas — Thu, 14 Aug 2014 04:03:56 GMT

@Patrick - Thanks so much for your input on lag function. This is very helpful!

Re: Must I reset the value of lag() when using BY variables?

dan_waldo — Thu, 14 Aug 2014 18:21:05 GMT

Thanks to everybody who replied -- this is very useful information.

Re: Must I reset the value of lag() when using BY variables?

Haikuo — Thu, 14 Aug 2014 18:35:07 GMT

At the risk of being obnoxious, I dare say 'never say never' . I can't think of any practical usage of conditional lag(), except I have had an rather unique request from a customer. say I have some balance record for checking accounts, they want to know the amount of the LAST over-draft if there is an existing one. Of course, there are many ways to do it, but lag() is also handy.

data have;

input balance @@;

cards;

100 -120 200 210 25 -35 400 100 20 -200

;

run;

data want;

set have;

if balance <0 then

last_overdraft=lag(balance);

run;

I guess the take-home message is that Being extremely cautious when using lag() under condition, use it only if you know what you are expecting.

My2cents,

Haikuo

Re: Must I reset the value of lag() when using BY variables?

ChrisHemedinger — Fri, 05 Sep 2014 12:01:14 GMT

And just to pile on with more LAG anecdotes and literature, I offer this blog post:

Pitfalls of the LAG function - The SAS Dummy

Echoing the other responses: if thinking through LAG makes your head hurt (as it does mine) and you can avoid using it...do. FIRST-dot and LAST-dot are powerful DATA step features that can solve many scenarios with elegance.

Chris

Re: Must I reset the value of lag() when using BY variables?

jakarman — Fri, 05 Sep 2014 12:16:46 GMT

No need for headaches for using lag it is more the common queue function http://en.wikipedia.org/wiki/Queue_(abstract_data_type)

With the implementation of that you see some problems as of the pull/push and sizing. SAS development could have done this in a better way.