*Hi, I have this dataset that I want to group by name and color, and count by this combination.
data have;
input name $ color $;
cards;
ann green
ann blue
ann yellow
ann blue
;
run;
*In the event that there are ties in count by 2 (or more) groups, I'd like to place the list of these groups in the same column.
data want;
input name $ color :$20. count;
cards;
ann green,yellow 1
ann blue 2
;
run;
*Thank you!
@angeliquec wrote:
No
Fine. Try:
proc summary data=work.have nway;
class name color;
output out=work.counted(drop=_type_ rename=(_freq_ = count));
run;
proc sort data=work.counted;
by name count;
run;
data work.want;
set work.counted;
by name count;
length colorList $ 50;
retain colorList;
if first.count then do;
colorList = ' ';
end;
colorList = catx(',', colorList, color);
if last.count then do;
output;
end;
drop color;
run;
Is it necessary to preserve the order of colors in the result dataset?
No
@angeliquec wrote:
No
Fine. Try:
proc summary data=work.have nway;
class name color;
output out=work.counted(drop=_type_ rename=(_freq_ = count));
run;
proc sort data=work.counted;
by name count;
run;
data work.want;
set work.counted;
by name count;
length colorList $ 50;
retain colorList;
if first.count then do;
colorList = ' ';
end;
colorList = catx(',', colorList, color);
if last.count then do;
output;
end;
drop color;
run;
You may have to fiddle with this slightly but it should get you going. You'll also need to test with more realistic data:
data have;
input name $ color $;
cards;
ann green
ann blue
ann yellow
ann blue
;
run;
proc sql;
create table counts as
select
*,
count(0) as count
from
have
group by
name, color
order by
name, count, color
;
quit;
data want (drop=color rename=(buffer=color));
format name buffer color count; * sets PDV order ;
length buffer $100;
do until (last.count);
set counts;
by name count color;
buffer=catx(',',buffer,color);
end;
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.