An interesting challenge was placed on the ACL User Forum on 11 Feb 2009.  It was asking for a way to take the value of a cell and alphabetize it.  It was an idea I had contemplated before, but never actually sat down to work.  Here is the solution I came up with:



OPEN Table
v_order = "ZYXWVUTSRQPONMLKJIHGFEDCBA"
v_order1=BLANK(100)
v_cycle = 26
v_cnt = 1
v_value = BLANK(100)

GROUP
  v_cycle = LEN(ALL(INCLUDE(UPPER(name),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
  v_order1 = INCLUDE(v_order,UPPER(name))
  v_cnt = 1
  LOOP WHILE v_cnt <= v_cycle
     V_value = INCLUDE(UPPER(name),SUB(v_order1,v_cnt,1)) + ALL(v_value)
     v_cnt = v_cnt + 1
  END
  Extract NAME ALL(v_value) as "alphabetized" TO TEMP1
  v_value = blank(100)
END


OPEN TEMP1

---

Here is the explanation as to how/why it works:



OPEN Table  <--- Open the table

v_order = "ZYXWVUTSRQPONMLKJIHGFEDCBA" <--set the order in reverse of desired order.
v_order1=BLANK(100) <---create a variable, this will be used to limit the size of the LOOP as LOOP can be a process killer.
v_cycle = 26 <---numeric value, doesn't really matter what it is, but I used 26 as a reminder that this represents the number of letters in the English Alphabet.
v_cnt = 1 <---Numeric value, again doesn't matter, but v_cnt in my naming conventions is a counter that always starts at 1 and progresses up.
v_value = BLANK(100) <--- a variable to capture the field contents in alphabetical order.

Lines of the GROUP command:
1) GROUP
2)    v_cycle = LEN(ALL(INCLUDE(UPPER(name),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
3)    v_order1 = INCLUDE(v_order,UPPER(name))
4)    v_cnt = 1
5)    LOOP WHILE v_cnt <= v_cycle
6)       V_value = INCLUDE(UPPER(name),SUB(v_order1,v_cnt,1)) + ALL(v_value)
7)       v_cnt = v_cnt + 1
8)    END
9)    Extract NAME ALL(v_value) as "alphabetized" TO TEMP1
10)   v_value = blank(100)
11) END


Detail on the GROUP command:
Line 1: Starts group.
Line 2: Creates a variable v_cycle that is equal to the number of ALPHA characters in the field name, this limits the size of the LOOP, thus reducing processing time.
Line 3: Creates a variable v_order1, which includes only those letters found in the field NAME, in the reverse order desired.  NOTE: If you were only looking for alpha characters and not worried about duplicate alpha characters, you would not need the loop.
Line 4: Resets v_cnt to 1
Line 5: Initiatesthe loop equal to the number of times that there is a unique alpha character in thefield NAME.
Line 6: adds the alpha character to the start of the v_value variable, if the alpha characters exists in the variable V_order1 at position v_cnt.
line 7: Progresses the v_cnt value one.
Line 8: ends the loop.
Line 9: extracts the final value
line 10: resets v_value
Line 11: ends the group    

---

---

David Coderre took the above script and improved upon it:

---

DIALOG (DIALOG TITLE "User Dialog" WIDTH 462 HEIGHT 295 ) (BUTTONSET TITLE "&OK;&Cancel" AT 348 12 DEFAULT 1 ) (TEXT TITLE "Select file to Open" AT 72 40 ) (ITEM TITLE "f" TO "v_infile" AT 84 96 WIDTH 253 ) (TEXT TITLE "cc. CAATS 2009" AT 324 244 )
Open %v_infile%
DIALOG (DIALOG TITLE "User Dialog" WIDTH 465 HEIGHT 338 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Select field to arrange alphabetically" AT 60 40 ) (ITEM TITLE "C" TO "v_field" AT 72 96 WIDTH 269 HEIGHT 136 )

delete alpha_file.fil ok
delete format alpha_file ok
v_max_char=0
v_ctr=1
v_str='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
alpha=blanks(100)
v_chars=blanks(100)

Group
  v_max_char = LEN(ALLTRIM(INCLUDE(UPPER(%v_field%),v_str)))
  v_chars = ALLTRIM(INCLUDE(v_str,UPPER(%v_field%)))
    LOOP while v_ctr<=v_max_char
     alpha=alltrim(alpha)+INCLUDE(UPPER(%v_field%),substr(v_chars,v_ctr,1))
     v_ctr=v_ctr+1
    END
Extract  %v_field% substr(exclude(alpha,' ')+blanks(100),1,100) as 'alpha' to alpha_file
  v_ctr=1
  alpha=blanks(100)
  v_chars=blanks(100)
END

Open alpha_file