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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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