☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Calcite | Level 5

shifted groupby

I would like from a table with 2 columns Group and Value, to create a new column shift_n_value
This column should be the column Value but N shifted by group.

For example if shift by one, the output should be :

 Group Value shift_n_value A 2 . A 1 2 A 3 1 A 1 3 B 4 . B 2 4 B 0 2

If i shift twice:

 Group Value shift_n_value A 2 . A 1 . A 3 2 A 1 1 B 4 . B 2 . B 0 4

Basically it is the equivalent in python to : df['shift_n_value'] = df.groupby('group')['value'].shift(n)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: shifted groupby

Your first is solved by

``````data want;
set have;
by group;
shift_n_value = lag(value);
if first.group then shift_n_value = .;
run;``````

For the second, you obviously need LAG2, so

``````data want;
set have;
by group;
shift_n_value = lag2(value);
if first.group
then count = 1;
else count + 1;
if count le 2 then shift_n_value = .;
drop count;
run;``````
2 REPLIES 2
Super User

Re: shifted groupby

Your first is solved by

``````data want;
set have;
by group;
shift_n_value = lag(value);
if first.group then shift_n_value = .;
run;``````

For the second, you obviously need LAG2, so

``````data want;
set have;
by group;
shift_n_value = lag2(value);
if first.group
then count = 1;
else count + 1;
if count le 2 then shift_n_value = .;
drop count;
run;``````
Super User

Re: shifted groupby

My second code can handle all cases by using a macro variable:

``````%let n = 2;

data want;
set have;
by group;
shift_n_value = lag&n.(value);
if first.group
then count = 1;
else count + 1;
if count le &n. then shift_n_value = .;
drop count;
run;``````
Discussion stats
• 2 replies
• 976 views
• 4 likes
• 2 in conversation