Solved
Contributor
Posts: 67

# LAG2 function by group

Hi,

I would be gratefull if anyone could tell me with lagging twice by group,

Lagging once I do this way:

``````if first.id_cst then
offer_kg1=.;
else
offer_kg1=lag(offer_kg);``````

but the option if.second dose not exist so I can not find the way to solve this problem.

Desirable result is

 Name Sex Age Lag2_age_by_sex Alice F 13 . Barbara F 13 . Carol F 14 13 Jane F 12 13 Janet F 15 14 Joyce F 11 12 Judy F 14 15 Louise F 12 11 Mary F 15 14 Alfred M 14 . Henry M 14 . James M 12 14 Jeffrey M 13 14 John M 12 12 Philip M 16 13 Robert M 12 12 Ronald M 15 16 Thomas M 11 12 William M 15 15

Thanks.

Accepted Solutions
Solution
‎08-11-2017 06:47 AM
Super User
Posts: 10,574

## Re: LAG2 function by group

[ Edited ]

Don't do this:

``````if first.id_cst then
offer_kg1=.;
else
offer_kg1=lag(offer_kg);``````

``````offer_kg1=lag(offer_kg);
if first.id_cst then offer_kg1=.;
``````

as only this method fills the lag queue reliably.

Now, for your question, you need a counter:

``````data want;
set have;
by sex;
if first.sex
then counter = 1;
else counter + 1;
lag2_age_by_sex = lag2(age);
if counter <= 2 then lag2_age_by_sex = .;
drop counter;
run;
``````

Edit: fixed the counter condition to <= 2

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎08-11-2017 06:47 AM
Super User
Posts: 10,574

## Re: LAG2 function by group

[ Edited ]

Don't do this:

``````if first.id_cst then
offer_kg1=.;
else
offer_kg1=lag(offer_kg);``````

``````offer_kg1=lag(offer_kg);
if first.id_cst then offer_kg1=.;
``````

as only this method fills the lag queue reliably.

Now, for your question, you need a counter:

``````data want;
set have;
by sex;
if first.sex
then counter = 1;
else counter + 1;
lag2_age_by_sex = lag2(age);
if counter <= 2 then lag2_age_by_sex = .;
drop counter;
run;
``````

Edit: fixed the counter condition to <= 2

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Thank you
Posts: 4,797

## Re: LAG2 function by group

@Matt3

If you use a LAG() function ensure that it gets executed in every single iteration of the data step - so never have it in an ELSE clause.

Below code should work:

``````proc sort data=sashelp.class out=work.class;
by sex;
run;

data want;
set work.class;
by sex;
lag2AgeBySex=lag2(age);
if lag2(sex) ne sex then call missing(lag2AgeBySex);
run;
``````
Super User
Posts: 9,840

## Re: LAG2 function by group

Post example test data in the form of a datastep in a code window (its the {i} above post) and what you want the output to look like.

By grouping in a dataset like this creates a first and last binary flag based on the first and last in the group, there is no second or third.  Also, lag is quite finickety.  I tend to find retaining a variable to be more effective in most scenarios.  As for helping with code, I have nothing to work with here, hence the need for test data/output - for instance you mention id_cst - this is never shown anywhere in the post.

☑ This topic is solved.