topic shifted groupby in New SAS User

shifted groupby

Aphreven — Tue, 12 Dec 2023 22:20:00 GMT

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)

Re: shifted groupby

Kurt_Bremser — Tue, 12 Dec 2023 22:30:34 GMT

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;

Re: shifted groupby

Kurt_Bremser — Tue, 12 Dec 2023 22:57:10 GMT

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;