topic Re: how to add a column with post values for same person in SAS Programming

how to add a column with post values for same person

Jie111 — Wed, 11 Sep 2024 15:02:22 GMT

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	        .

Re: how to add a column with post values for same person

Tom — Wed, 11 Sep 2024 15:34:28 GMT

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;

Re: how to add a column with post values for same person

mkeintz — Thu, 12 Sep 2024 17:29:15 GMT

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