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.
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
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
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.