topic Re: Long to Wide with Concatenate in SAS Programming

Long to Wide with Concatenate

angeliquec — Thu, 14 Mar 2019 05:33:13 GMT

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

Re: Long to Wide with Concatenate

andreas_lds — Thu, 14 Mar 2019 05:54:20 GMT

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

Re: Long to Wide with Concatenate

angeliquec — Thu, 14 Mar 2019 06:22:28 GMT

Re: Long to Wide with Concatenate

ScottBass — Thu, 14 Mar 2019 06:59:04 GMT

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;

Re: Long to Wide with Concatenate

andreas_lds — Thu, 14 Mar 2019 06:27:08 GMT

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