CODEWITHSUNDEEP
formatsas
Stu
Stu

Reputation: 1653

Rearrange character column using "PROC FORMAT" in SAS

I want to take the follow data variable:

"Nebraska-Iowa"
"Washington-Arkansas"
"Illinois-Utah"

and transform it so that it orders the character groups around the hyphen to be in alphabetical order:

"Iowa-Nebraska"
"Arkansas-Washington"
"Illinois-Utah"

Is there an easy way to do this? I need to split the string around the hyphen, rearrange if necessary, and than paste back together.

UPDATE

After playing with Matthew's answer, I have decide to generalize this for any number of states with the following dataset:

Nebraska-Iowa
Washington-Arkansas-Texas
Illinois-Utah
Colorado

Here is the code I am trying to build. What I am struggling with is building an array that I loop through, pull out the appropriate word, and then pasting them back together after arranging. Please help!

/*Example dataset*/
data have;
    format text $50.;
    input text;
    datalines;  
Nebraska-Iowa
Washington-Arkansas-Texas
Illinois-Utah
Colorado
run;

/*Rearrange strings in dataset*/
data arrangestrings;
    set have;
    length result $50;
    howmanyb = countc(text,'-');
    howmany = howmanyb + 1;
    array state[howmany] _character_;
    do i=1 to howmany;
         state[i] = scan(text, i, '-');
    end;
    call sortc(of state(*));
    result = catx("-", state[*]);
    keep result;
run;

Upvotes: 0

Views: 218

Answers (1)

Matthew Nizol
Matthew Nizol

Reputation: 2659

I don't think you need to go to the trouble of defining a user-defined format for a task like this. The built-in scan method is your friend here:

data have;
    format text $50.;
    input text;
    datalines;  
Nebraska-Iowa
Washington-Arkansas
Illinois-Utah
run;


data want;
    set have;
    length word1 word2 result $50;
    word1 = scan(text, 1, '-');
    word2 = scan(text, 2, '-');
    result = ifc(word1 <= word2, text, catx('-', word2, word1));
run;

proc print data=want;
run;

Check out the documentation on the built-in functions that I used (scan, ifc, catx) if you're not familiar with them:

http://support.sas.com/documentation/cdl/en/allprodslang/67244/HTML/default/viewer.htm#syntaxByType-function.htm

Upvotes: 3

Related Questions