Retain + Looping among multiple row record

[jcsimmo]

Hi there, I am trying to take a score recorded at each time interval for a subject and add columns with that score. For example I want to turn a table like this:

Study_ID	Month	Score
812	0	56
812	12	37
812	24	36
812	36	35
812	48	34
812	60	33
813	0	61
813	3	51
813	12	50
813	24	49
813	36	48
815	0	47
815	3	43
815	12	42
815	24	41
815	48	39

Into a table like this

Study_ID	Month	Score	Score0	Score3	Score12	Score24	Score36	Score48	Score60
812	0	56	59	.	37	36	35	34	33
812	12	37	59	.	37	36	35	34	33
812	24	36	59	.	37	36	35	34	33
812	36	35	59	.	37	36	35	34	33
812	48	34	59	.	37	36	35	34	33
812	60	33	59	.	37	36	35	34	33
813	0	61	61	51	50	49	48	.	.
813	3	51	61	51	50	49	48	.	.
813	12	50	61	51	50	49	48	.	.
813	24	49	61	51	50	49	48	.	.
813	36	48	61	51	50	49	48	.	.
815	0	47	47	43	42	41	.	39	.
815	3	43	47	43	42	41	.	39	.
815	12	42	47	43	42	41	.	39	.
815	24	41	47	43	42	41	.	39	.
815	48	39	47	43	42	41	.	39	.

I tried the following codes:

proc sort data=have;
by study_id;
run;

proc sql;
select max(count) into :max_count separated by ' ' from
(select count(study_ID) as count from have
group by study_ID);
quit;

data want;
do _n_=1 by 1 until(last.study_ID);
set have;
by study_ID;
array SCORE(*) SCORE0 SCORE3 SCORE12 SCORE24 SCORE36 SCORE48 SCORE60;
SCORE(_n_)=SCORE;

end;

do until(last.study_ID);
set have;
by study_ID;
output;
end;
run;

Also tried:

proc sql; (adapted from @Reeza)
select max(count) into :max_count separated by ' ' from
(select count(study_ID) as count from have
group by study_ID);
quit;

proc sort data=have;
by study_id month;
run;

proc transpose data=have out=want prefix=Score;

by study_ID;

var Score;

run;

And then did a left-join for the table. However both don't take into account months where a score wasn't recorded so I end up with a table like this

Study_ID	Month	Score	Score_0	Score_3	Score_12	Score_24	Score_36	Score_48	Score_60
812	0	56	59	37	36	35	34	33
812	12	37	59	37	36	35	34	33
812	24	36	59	37	36	35	34	33
812	36	35	59	37	36	35	34	33
812	48	34	59	37	36	35	34	33
812	60	33	59	37	36	35	34	33
813	0	61	61	51	50	49	48	.	.
813	3	51	61	51	50	49	48	.	.
813	12	50	61	51	50	49	48	.	.
813	24	49	61	51	50	49	48	.	.
813	36	48	61	51	50	49	48	.	.
815	0	47	47	43	42	41	39	.
815	3	43	47	43	42	41	39	.
815	12	42	47	43	42	41	39	.
815	24	41	47	43	42	41	39	.
815	48	39	47	43	42	41	39	.

Where the missing months shifts the remaining scores to the left.

Thanks,

Jon

[mkeintz]

You can address this simply via:

1.  Reading each id twice, first time to populate the SCORE0, SCORE3, ... SCORE60 variables (retained).  Second time to output the now complete set of values.
2. Make an array of SCORE0 through SCORE60, indexed by the month value, making for simple use of the MONTH value (as an array index) to populate the variables.
3. Judiciously keep a subset of SCORE0 through SCORE60.

data have;
  input Study_ID	Month	Score;
datalines;
812	0	56
812	12	37
812	24	36
812	36	35
812	48	34
812	60	33
813	0	61
813	3	51
813	12	50
813	24	49
813	36	48
815	0	47
815	3	43
815	12	42
815	24	41
815	48	39
run;

data want (keep=study_id month score
                score0 score3 score12 score24 score36 score48 score60);

  set have (in=firstpass)
      have (in=secondpass);
  by study_id;

  array _sc{0:60} score0-score60;
  retain score0-score60 ;

  if first.study_id then call missing(of _sc{*});
  if firstpass then _sc{month}=score;
  if secondpass;
run;

Note that the array _sc has a lower bound of 0 and upper bound of 60 (and names to match), which will satisfy the minimum and maximum expected value of MONTH.  Of course this creates 61 variables, most of which are not kept.  But it wonderfully simplifies the use of MONTH as an index pointing to the desired array element.

[r_behata]

data have;
input Study_ID Month Score;
datalines;
812 0 56
812 12 37
812 24 36
812 36 35
812 48 34
812 60 33
813 0 61
813 3 51
813 12 50
813 24 49
813 36 48
815 0 47
815 3 43
815 12 42
815 24 41
815 48 39
run;

data want(keep=study_id month score score0 score3 score12 score24 score36 
		score48 score60);
	array _score{0:60} score0-score60;
	call missing(of _score[*]);

	do _n_=1 by 1 until(last.Study_ID);
		set have;
		by Study_ID;
		_score[month]=score;
	end;

	do _n_=1 to _n_;
		set have;
		output;
	end;
run;

[Ksharp]

data have;
  input Study_ID	Month	Score;
datalines;
812	0	56
812	12	37
812	24	36
812	36	35
812	48	34
812	60	33
813	0	61
813	3	51
813	12	50
813	24	49
813	36	48
815	0	47
815	3	43
815	12	42
815	24	41
815	48	39
;
proc transpose data=have out=temp(drop=_name_) prefix=score_;
by study_id;
var month score;
id month;
run;
proc sql noprint;
select cats('score_',month) into :list separated by ' ' from have order by month;
quit;
data want;
retain Study_ID	Month	Score &list ;
 merge have temp;
 by study_id;
run;