BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Matt3
Quartz | Level 8

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Quartz | Level 8
Thank you 🙂
Patrick
Opal | Level 21

@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
Diamond | Level 26 RW9
Diamond | Level 26

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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