DATA Step, Macro, Functions and more

LAG2 function by group

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

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

NameSexAgeLag2_age_by_sex
AliceF13.
BarbaraF13.
CarolF1413
JaneF1213
JanetF1514
JoyceF1112
JudyF1415
LouiseF1211
MaryF1514
AlfredM14.
HenryM14.
JamesM1214
JeffreyM1314
JohnM1212
PhilipM1613
RobertM1212
RonaldM1516
ThomasM1112
WilliamM1515

 

 

Thanks.

 

 

 

 


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

Re: LAG2 function by group

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


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

Re: LAG2 function by group

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 53

Re: LAG2 function by group

Posted in reply to KurtBremser
Thank you Smiley Happy
Respected Advisor
Posts: 4,173

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
Super User
Posts: 7,942

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 137 views
  • 2 likes
  • 4 in conversation