I have a dataset like below. I need to calculate the difference between the previous and next score.
study_id year_for_score score 175004173018 2003 4.5 175004173018 2004 4.5 175004173018 2005 6.5 175004173018 2006 6.5 175102254399 2000 8 175102254399 2001 8 175299202118 1996 5.5 175299202118 1997 5.5
How could I create a column like below "score_next"?
study_id year_for_score score score_next 175004173018 2003 4.5 4.5 175004173018 2004 4.5 6.5 175004173018 2005 6.5 6.5 175004173018 2006 6.5 . 175102254399 2000 8 8 175102254399 2001 8 . 175299202118 1996 5.5 5.5 175299202118 1997 5.5 .
It is much easier to remember something than to predict the future.
proc sort data=HAVE;
by study_id descending year_for_score;
run;
data WANT;
set HAVE;
by study_id;
score_next=lag(score);
if first.study_id then call missing(score_next);
run;
It is much easier to remember something than to predict the future.
proc sort data=HAVE;
by study_id descending year_for_score;
run;
data WANT;
set HAVE;
by study_id;
score_next=lag(score);
if first.study_id then call missing(score_next);
run;
@Tom wrote:
It is much easier to remember something than to predict the future.
I do not agree with this premise. The following does not require a preliminary descending sort (and possibly a posterior ascending sort for later data management or analysis). It's a couple extra lines beyond the call to a LAG function but is still pretty easy.
data have;
input study_id year_for_score score;
datalines;
175004173018 2003 4.5
175004173018 2004 4.5
175004173018 2005 6.5
175004173018 2006 6.5
175102254399 2000 8
175102254399 2001 8
175299202118 1996 5.5
175299202118 1997 5.5
run;
data want;
set have (keep=study_id);
by study_id;
merge have
have (firstobs=2 keep=score rename=(score=next_score));
if last.study_id then call missing(next_score);
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.