How to put words in alphabetical order that are separated by comma

[arde]

Hi.

I have data in a cell that  I would like alphabetized:

Have:

John	C Plus,B,A
Sarah	D,A Plus,C Minus

Want:

John	A,B,C Plus
Sarah	A Plus,C Minus,D

Thank you in advance for your efforts

[ballardw · Accepted solution]

Split the words out into separate variables.

Sort those variables

Recombine the variables in order into the current variable

remove the other variables.

You really should provide data in the form of working data step code if you want tested code. at least provide data set names and variable names. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Untested as no data set provided:

data want;
    set have;
   /* the 5 below should be replaced the maximum number of 
     "words" in the list, 10 should be replaced with the maximum
     length of any of the words
   */
    array t ( 5) $ 10;
   /* countw here gets the number of elements separated by comma*/
    do i=1 to countw(yourvariablename,',');
       /* get the ith "word" separated by commas and assign to an
          element of the array*/
       t[i] = scan(yourvariablename,i,',');
   end;
   /* sort the values this actually places and blanks at the front*/
   call sortc(of t(*));
   /* catx combines values after removing trailing spaces placing the 
      first  parameter, comma in this case between results*/
   yourvariablename = catx(',', of t(*));
  /* drop the unwanted variables the 5 here should match the number of elements defined for the array*/
   drop i  t1-t5;
run;

of t(*) is the way to refer to all elements of an array for those functions that will accept multiple variables.

[arde]

thank you so much!

[Kurt_Bremser]

A method where the number of elements must not be determined beforehand:

data long;
set have;
if _n_ = 1 then call symputx("l",vlength(string));
do i = 1 to countw(string,",");
  s = scan(string,i,",");
  output;
end;
keep name s;
run;

proc sort data=long;
by name s;
run;

data want;
set long;
by name;
length string $&l.;
retain string;
if first.name
then string = s;
else string = catx(",",string,s);
if last.name;
keep name string;
run;

[arde]

Thank you!