Solved
Contributor
Posts: 35

# Combine rows of data

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

Accepted Solutions
Solution
‎08-30-2016 02:50 PM
Super User
Posts: 6,785

## Re: Combine rows of data

[ Edited ]

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;

All Replies
Super User
Posts: 5,884

## Re: Combine rows of data

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.

Data never sleeps
Solution
‎08-30-2016 02:50 PM
Super User
Posts: 6,785

## Re: Combine rows of data

[ Edited ]

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;

Contributor
Posts: 35

## Re: Combine rows of data

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.

Posts: 3,852

## Re: Combine rows of data

``````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;``````
☑ This topic is solved.