Dataset Collapse by ID

[radhikaa4]

Hello. I would like to use PROC SQL command to collapse by group.

For example here is the dataset:

subjectid	pmh_1	pmh_2	pmh_3	pmh_4
1	1	0	0	0
1	0	1	0	0
1	0	0	0	1
1	0	0	1	0
2	0	0	0	1
2	0	0	1	0
2	1	0	0	0

I want this output (one subject Id per row but summed )

subjectid	pmh_1	pmh_2	pmh_3	pmh_4
1	1	1	1	1
2	1	0	1	1

I tried having and group by clause but it wont do much

Thanks so much for your help!

[Reeza]

---

@radhikaa4 wrote:

Hello. I would like to use PROC SQL command to collapse by group.

For example here is the dataset:

subjectid	pmh_1	pmh_2	pmh_3	pmh_4
1	1	0	0	0
1	0	1	0	0
1	0	0	0	1
1	0	0	1	0
2	0	0	0	1
2	0	0	1	0
2	1	0	0	0

I want this output (one subject Id per row but summed )

subjectid	pmh_1	pmh_2	pmh_3	pmh_4
1	1	1	1	1
2	1	0	1	1

I tried having and group by clause but it wont do much

Thanks so much for your help!

---

Please post what you've tried so we can help you out.

SQL should work fine and this is fairly simple calculation using either MAX or SUM depending on your logic and requirements.

[ballardw]

---

@radhikaa4 wrote:

Hello. I would like to use PROC SQL command to collapse by group.

For example here is the dataset:

subjectid	pmh_1	pmh_2	pmh_3	pmh_4
1	1	0	0	0
1	0	1	0	0
1	0	0	0	1
1	0	0	1	0
2	0	0	0	1
2	0	0	1	0
2	1	0	0	0

I want this output (one subject Id per row but summed )

subjectid	pmh_1	pmh_2	pmh_3	pmh_4
1	1	1	1	1
2	1	0	1	1

I tried having and group by clause but it wont do much

Thanks so much for your help!

---

Show the code and explain why you think it "wont do much".

[novinosrin]

I concur with others in the thread. I am jumping in the thread to have my argument against sql for this exercise for the reason sql doesn't support variable lists making it inconvenient to type, that is more applicable to especially lazy folks like me. When you have an optimized pre written procs assuming you are using SAS, the convention is to take advantage of it.

data have;
input subjectid	pmh_1	pmh_2	pmh_3	pmh_4;
cards;
1	1	0	0	0
1	0	1	0	0
1	0	0	0	1
1	0	0	1	0
2	0	0	0	1
2	0	0	1	0
2	1	0	0	0
;

proc means data=have noprint;
by subjectid;
var pmh_:;
output out=want(drop=_:) sum=;
run;