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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.