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
You can address this simply via:
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.
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.