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!
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;
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.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.