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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.