topic Re: Dataset Collapse by ID in SAS Data Management

Dataset Collapse by ID

radhikaa4 — Thu, 06 Sep 2018 16:01:01 GMT

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!

Re: Dataset Collapse by ID

Reeza — Thu, 06 Sep 2018 16:13:47 GMT

@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.

Re: Dataset Collapse by ID

ballardw — Thu, 06 Sep 2018 16:45:40 GMT

@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".

Re: Dataset Collapse by ID

novinosrin — Thu, 06 Sep 2018 16:57:35 GMT

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;