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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.