BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

*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!

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

@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;

View solution in original post

4 REPLIES 4
andreas_lds
Jade | Level 19

Is it necessary to preserve the order of colors in the result dataset?

andreas_lds
Jade | Level 19

@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;
ScottBass
Rhodochrosite | Level 12

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;

 

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 871 views
  • 2 likes
  • 3 in conversation