DATA Step, Macro, Functions and more

Combine rows of data

Accepted Solution Solved
Reply
Contributor zz
Contributor
Posts: 28
Accepted Solution

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

 

How should I go about it, please?

 

Thank you for your help in advance!


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

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;

View solution in original post


All Replies
Super User
Posts: 5,424

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: 5,498

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 zz
Contributor
Posts: 28

Re: Combine rows of data

Posted in reply to Astounding

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.  

Respected Advisor
Posts: 3,799

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 297 views
  • 3 likes
  • 4 in conversation