LAG2 function by group

[Matt3]

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.

[Kurt_Bremser · Accepted solution]

Don't do this:

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

Instead do this:

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

[Matt3]

Thank you 🙂

[Patrick]

@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;

[RW9]

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.