topic Re: how to merge observations with repeated data (format: wide) ? in SAS Procedures

how to merge observations with repeated data (format: wide) ?

EmmanuelS — Sat, 14 Mar 2015 08:20:38 GMT

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.

Re: how to merge observations with repeated data (format: wide) ?

Patrick — Sat, 14 Mar 2015 08:29:11 GMT

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.

Re: how to merge observations with repeated data (format: wide) ?

Ksharp — Sat, 14 Mar 2015 09:16:15 GMT

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

Re: how to merge observations with repeated data (format: wide) ?

Tom — Sat, 14 Mar 2015 13:17:43 GMT

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;