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

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	        .
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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;
mkeintz
PROC Star

@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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1141 views
  • 2 likes
  • 3 in conversation