topic Re: How to put words in alphabetical order that are separated by comma in SAS Programming

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

arde — Thu, 03 Aug 2023 02:07:46 GMT

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

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

ballardw — Thu, 03 Aug 2023 02:38:54 GMT

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-data-AKA-generate/ta-p/258712 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.

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

Kurt_Bremser — Thu, 03 Aug 2023 07:56:03 GMT

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;

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

arde — Thu, 03 Aug 2023 10:51:30 GMT

thank you so much!

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

arde — Thu, 03 Aug 2023 10:51:40 GMT

Thank you!