topic Combine rows of data in SAS Programming

Combine rows of data

zz — Tue, 30 Aug 2016 14:29:06 GMT

I have a dataset like below:

Subj C1 C2

S01 Y N

S01 N N

S02 Y N

S02 N Y

I need to combine the rows with distinct Subjs as below:

Subj C1 C2

S01 Y N

S02 Y Y

How should I go about it, please?

Thank you for your help in advance!

Re: Combine rows of data

LinusH — Tue, 30 Aug 2016 14:32:35 GMT

If I understand right, Y has precedence over N?

If those were 1 and 0 respectively (and you could have Y and N as a SAS format instead for display purposes), you could use the max() SQL aggregate function.

Re: Combine rows of data

Astounding — Tue, 30 Aug 2016 17:36:01 GMT

Actually, the SAS implementation of SQL permits using the MAX function on character fields:

proc sql;

create table want as select Subj, max(C1) as C1, max(C2) as C2 , count(*) as nrows

from have

group by subj;

quit;

Re: Combine rows of data

data_null__ — Tue, 30 Aug 2016 14:47:19 GMT

data C;
   input (Subj   C1    C2)($);
   cards;
S01    Y      N
S01    N      N
S02    Y      N
S02    N      Y
;;;;
   run;
proc print;
   run;
proc summary data=c nway;
   class subj;
   output out=temp 
      idgroup(max(c1) out(c1)=)
      idgroup(max(c2) out(c2)=)
      ;
   run;
proc print;
   run;

Re: Combine rows of data

zz — Tue, 30 Aug 2016 17:15:41 GMT

There is one additional detail: I need to count the number of rows for each subject, while combining rows.

Thank you for your below implementation! It is exactly what I'd need.