topic Re: LAG Function in SAS Programming

LAG Function

jaiganesh — Wed, 27 Feb 2019 15:42:31 GMT

Hello,

Can anybody help me to create the code which make the cumulative sum for the variable Marks used in below data set with LAG Keyword/Function.

Input:

data kk;
input id names$ marks;
cards;
1 jai 10
2 sai 20
3 ram 30
4 shyam 40
5 hari 50
;
run;

output Should be

id names marks New_marks

1 jai 10 10
2 sai 20 30
3 ram 30 60
4 shyam 40 100
5 hari 50 150

Please provide the output only with using LAG function not Looking with Retain Keyword.

Regards,

Jaiganesh

Re: LAG Function

andreas_lds — Wed, 27 Feb 2019 15:45:52 GMT

And why don't you want to use retain? Have you read the examples in the online docs? This is such an easy task, it should be solvable with some research.

Re: LAG Function

jaiganesh — Wed, 27 Feb 2019 16:51:32 GMT

I've been using Retain Keyword, one of my colleague has raise the concern of LAG function functionality and it's application in programming, Let me know if you need any other information.

Re: LAG Function

Kurt_Bremser — Wed, 27 Feb 2019 16:59:14 GMT

Maxim 14: Use the Right Tool, in this case retain.

Re: LAG Function

Reeza — Wed, 27 Feb 2019 18:44:41 GMT

If you show an attempt, happy to help you work through it.

The documentation has some examples that are pretty helpful if you work through them first:

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=n0l66p5oqex1f2n1quuopdvtcjqb.htm&locale=en#p0upu4wnn0xtwvn1p1bqaano69do

@jaiganesh wrote:

Hello,

Can anybody help me to create the code which make the cumulative sum for the variable Marks used in below data set with LAG Keyword/Function.

Input:

data kk;
input id names$ marks;
cards;
1 jai 10
2 sai 20
3 ram 30
4 shyam 40
5 hari 50
;
run;

output Should be

id names marks New_marks
1 jai 10 10
2 sai 20 30
3 ram 30 60
4 shyam 40 100
5 hari 50 150

Please provide the output only with using LAG function not Looking with Retain Keyword.

Regards,

Jaiganesh

Re: LAG Function

jaiganesh — Wed, 27 Feb 2019 20:01:06 GMT

Mentioned Link does not help me much with respect to certain with LAG Function cumulative addition.

Can you provide anything else

Re: LAG Function

Kurt_Bremser — Wed, 27 Feb 2019 20:03:52 GMT

@jaiganesh wrote:
Mentioned Link does not help me much with respect to certain with LAG Function cumulative addition.

Can you provide anything else

Because the lag() function is NOT the right tool for your task, period.

Re: LAG Function

PaigeMiller — Wed, 27 Feb 2019 20:38:34 GMT

@jaiganesh wrote:
Mentioned Link does not help me much with respect to certain with LAG Function cumulative addition.

Can you provide anything else

Many people are saying that LAG is not the right tool for this purpose. What more do you need? Furthermore, there is no documentation that we can point to that says function X should not be used to perform Task A. People don't write documentation listing all the things that a specific function can't do.

Re: LAG Function

ErikLund_Jensen — Wed, 27 Feb 2019 20:31:57 GMT

Hi @jaiganesh

The Lag function returns the value from a previous input record. Lag1 (or just Lag) returns the value from the record preceding the current record, Lag2 from the one before that and so on. There is no limit on the number of Lags other than the available memory, so if you have 1000 records, you can use Lag1 - Lag999.

But you cannot use Lag on variables created in the data step, so you cannot accumulate values using Lag. Here is an example using Lag on your data to give the wanted result, so you can see what happens:

data kk;
input id names$ marks;
cards;
1 jai 10
2 sai 20
3 ram 30
4 shyam 40
5 hari 50
;
run;

data want; set kk; 
	lag1marks = lag1(marks);
	lag2marks = lag2(marks);
	lag3marks = lag3(marks);
	lag4marks = lag4(marks);
	New_marks = sum(marks,lag1marks,lag2marks,lag3marks,lag4marks);
run;

This works with your 5 observations, because the code uses Lag1 - Lag4, but it cannot be made dynamic, at least not without a lot of macro coding. If there were more observations, it would give a rolling sum over the last 5 observarions, which might be useful in some cases, but to use Lag to make a total summation over a column in a data set is - politely expressed - not advisable.

Re: LAG Function

jaiganesh — Wed, 27 Feb 2019 20:58:36 GMT

Thanks a lot for you time and effort.

Regards,

Jaiganesh

Re: LAG Function

ChrisNZ — Wed, 27 Feb 2019 21:08:15 GMT

The function LAG is no appropriate here.

Ask your colleague to enlighten us and show us how it's done.

Note this was already asked by someone else and on the same day (and with equivalent replies).

https://communities.sas.com/t5/New-SAS-User/How-to-calculate-cumulative-sum-using-Lag-function/td-p/538908

Colleague or classmate?

Re: LAG Function

FreelanceReinh — Wed, 27 Feb 2019 23:31:10 GMT

Hi @jaiganesh,

I fully agree with all the others that the LAG function is not ideal for this task (to say the least). But you could use this (inefficient) code in order to apply it:

data _null_;
set kk end=last;
if _n_=1 then call execute('data want; set kk;');
call execute('new_marks=sum(marks,lag(new_marks));');
if last then call execute('run;');
run;

Edit: Or this:

data want;
set kk;
do _n_=0, 1;
  if _n_ then output;
  new_marks=sum(marks,lag(new_marks));
end;
run;

Re: LAG Function

Kurt_Bremser — Fri, 01 Mar 2019 12:39:00 GMT

I'm really puzzled.

What is the functional difference between

data want; set kk; do _n_=0, 1; if _n_ then output; new_marks=sum(marks,lag(new_marks)); end; run;

and

data want2;
set kk;
new_marks=sum(marks,lag(new_marks));
output;
new_marks=sum(marks,lag(new_marks));
run;

(the first works, the second not, although the summarization and output statements execute in the same order)

Re: LAG Function

FreelanceReinh — Fri, 01 Mar 2019 13:53:03 GMT

The crucial point is that each occurrence of the LAG function in the DATA step corresponds to a separate queue.

So, in the first code one queue operates twice: The idea is that the queue should always output the previous cumulative sum (to be added to the current value of MARKS in order to obtain the desired value of NEW_MARKS). This happens in the first iteration of the DO loop. The second iteration of the loop is required because the calculated value of NEW_MARKS must be fed into the queue, so that it will be output in the next call of the LAG function (in the next iteration of the DATA step). This cannot be accomplished during the calculation because obviously the value just being calculated is not available yet and hence a missing value is fed into the queue (since NEW_MARKS is not RETAINed). This missing value is then returned in the second iteration of the DO loop, leading (temporarily) to a "wrong" value of NEW_MARKS, namely NEW_MARKS=MARKS, but this doesn't matter because it happens after the OUTPUT statement.

The second code creates two separate queues. The first one does always the same in each iteration of the DATA step:

It outputs a missing value (in the first iteration of the DATA step this is the usual behavior in the first call of any LAG function, in subsequent iterations it's because of item 2 below).
It stores a missing value because the unretained variable NEW_MARKS has been initialized to missing and not yet assigned a value at the time when the corresponding LAG function is called.

Hence, the value of NEW_MARKS which is output is always SUM(MARKS, .) = MARKS.

The second queue stores the value of NEW_MARKS created in the previous assignment statement (i.e. [with the data in dataset kk] 10, 20, 30, ... -- the values of MARKS, see above) and outputs the previous one, thus assigning temporary values 10, 30, 50, 70, ... to NEW_MARKS, but these aren't output to dataset WANT2 anyway, nor are they retained. So, the second assignment statement is useless.

Re: LAG Function

Kurt_Bremser — Fri, 01 Mar 2019 15:01:53 GMT

That's the explanation. Two queues vs. one queue.

Thank you very much.

Maxim 13 in action.

Re: LAG Function

ErikLund_Jensen — Fri, 01 Mar 2019 18:03:28 GMT

@FreelanceReinh

That was really interesting, thank you so much! - I am sorry to have mislead @jaiganesh, but I never realized that lag is not coupled to the input buffer, but works on any assignment, so a construction like this works:

data _null_;
    do a = 1 to 5;
        b = lag(a);
        c = lag(b);
        d = lag(c);
        e = lag(d);
        put a= b= c= d= e=;
    end;
run;

a=1 b=. c=. d=. e=.
a=2 b=1 c=. d=. e=.
a=3 b=2 c=1 d=. e=.
a=4 b=3 c=2 d=1 e=.
a=5 b=4 c=3 d=2 e=1