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

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 : 

GroupValueshift_n_value
A2.
A12
A31
A13
B4.
B24
B02

If i shift twice:

 

GroupValueshift_n_value
A2.
A1.
A32
A11
B4.
B2.
B04


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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

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;
Kurt_Bremser
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

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