Hello,
New user in SAS, I would like a hand about how to merge observations with repeated data.
Because a good drawing is better than a bad explanation, here are 2 tables:
1°) what I have:
patient number | color | congruence | trial | t1 | t2 | t3 | t4 |
1 | GREEN | NO | ERROR | 262 | 588 | 510 | 714 |
1 | RED | NO | ERROR | 931 | 449 | 307 | 460 |
1 | BLUE | NO | ERROR | 672 | 654 | 592 | |
1 | YELLOW | NO | ERROR | 752 | 568 | ||
1 | GREEN | NO | GOOD | 743 | 697 | 999 | |
1 | RED | NO | GOOD | 1087 | 630 | 590 | |
1 | BLUE | NO | GOOD | 192 | 240 | ||
1 | YELLOW | NO | GOOD | 235 | 756 | 345 | |
1 | GREEN | YES | ERROR | 127 | 345 | ||
1 | RED | YES | ERROR | 873 | 234 | 123 | 638 |
1 | BLUE | YES | ERROR | 345 | 432 | 765 | |
1 | YELLOW | YES | ERROR | 567 | 341 | ||
1 | GREEN | YES | GOOD | 321 | 795 | 432 | |
1 | RED | YES | GOOD | 234 | 693 | ||
1 | BLUE | YES | GOOD | 321 | 432 | 674 | 412 |
1 | YELLOW | YES | GOOD | 324 | 756 |
2°) what I (would) want:
patient number | color | congruence | trial | t1 | t2 | t3 | t4 | t5 | t6 | t7 | t8 | t9 | t10 | t11 | t12 | t13 |
1 | null | NO | ERROR | 262 | 588 | 510 | 714 | 931 | 449 | 307 | 460 | 672 | 654 | 592 | 752 | 568 |
1 | null | NO | GOOD | 743 | 697 | 999 | 1087 | 630 | 590 | 192 | 240 | 235 | 756 | 345 | ||
1 | null | YES | ERROR | 127 | 345 | 873 | 234 | 123 | 638 | 345 | 432 | 765 | 567 | 341 | ||
1 | null | YES | GOOD | 321 | 795 | 432 | 234 | 693 | 321 | 432 | 674 | 412 | 324 | 756 |
The idea is to merge observations belonging to the same individual, by color. The information about color is not important and this variable can be removed,
unfortunately I cannot write the code to perform this task. Thank you very much if one could help.
ES
Looking at your data isn't it simply so that you want to "merge" by Patient Number, Congruence and Trial? If so then what I would do:
1) Organize your data in a long format with columns "patient_number, congruence, trial, t"
2) Use Proc Transpose with by group processing "by patient_number congruence trial".
For code examples: Please provide a data step creating your source data set.
good form data are needed to input .
data have;
input patient_number color $ congruence $ trial $ t1 t2 t3 t4 ;
cards;
1 GREEN NO ERROR 262 588 510 714
1 RED NO ERROR 931 449 307 460
1 BLUE NO ERROR 672 654 592 .
1 YELLOW NO ERROR 752 568 . .
1 GREEN NO GOOD 743 697 999 .
1 RED NO GOOD 1087 630 590 .
1 BLUE NO GOOD 192 240 .
1 YELLOW NO GOOD 235 756 345 .
;
run;
proc transpose data=have out=temp;
by patient_number congruence trial color notsorted;
var t1-t4;
run;
proc transpose data=temp out=want(drop=_:);
by patient_number congruence trial;
id _name_ color;
var col1;
run;
Xia Keshan
That is almost what is wanted. You need to eliminate the ID statement from the last transpose. Also you need to eliminate the missing values .
data have;
input patient_number color $ congruence $ trial $ t1 t2 t3 t4 ;
cards;
1 GREEN NO ERROR 262 588 510 714
1 RED NO ERROR 931 449 307 460
1 BLUE NO ERROR 672 654 592 .
1 YELLOW NO ERROR 752 568 . .
1 GREEN NO GOOD 743 697 999 .
1 RED NO GOOD 1087 630 590 .
1 BLUE NO GOOD 192 240 . .
1 YELLOW NO GOOD 235 756 345 .
;
run;
proc transpose data=have out=temp(where=(col1 ne .));
by patient_number congruence trial color notsorted;
var t1-t4;
run;
proc transpose data=temp out=want(drop=_:) prefix=T;
by patient_number congruence trial;
var col1;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.