MyVar.MyTail = 'hello' MyVar.1.3 = 'hell' MyVar.5 = 'h'If you perform a SORT on the stem MyVar., then the 3 values above will be sorted, and the names of the variables will be changed so that they reflect the new sorted order. Assuming ascending sort order, then the lowest value (in the above example, that is "h") will be reassigned to a variable name (Stem).1, the next lowest value (ie, "hell") will be assigned to (Stem).2, the next lowest value (ie, "hello") assigned to (Stem).3, etc. (Stem).0 will be set to a count of how many tails were sorted. So after SORT'ing, it will be as if your script had instead done this:
MyVar.0 = 3 MyVar.1 = 'h' MyVar.2 = 'hell' MyVar.3 = 'hello'The variables MyVar.MyTail, MyVar.1.3, and MyVar.5 will no longer have any values.
Syntax of a SORT statement
A SORT statement works in a similar way to PARSE VAR. It has the syntax:
SORT StemName. templatewhere StemName. is the name of some stem variable whose tails are to be sorted, and the optional template is a lot like a PARSE template in that it can have positional offsets, and string patterns to match, as well as placeholders.
Note: All tails of the stem are sorted. It is not possible to exclude any tails from being sorted. A tail with the name StemName.0 is ignored in the sort, and its value will be overwritten (with the "sort count") after the SORT.
Let's say that you have the following data you wish sorted:
MyVar.MyTail = 'hello' MyVar.1.3 = 'hell' MyVar.5 = 'h'You would simply specify the SORT keyword followed by the name of the Stem variable to sort, followed by an "A":
SORT MyVar. AThe A indicates the primary sort criteria, which in this case is the entire value of each variable since there is no further refinement of the template. And the result would be as if you had originally done:
MyVar.0 = 3 MyVar.1 = 'h' MyVar.2 = 'hell' MyVar.3 = 'hello'
Sort by offset and length
Assume you want to do a sort based upon some substring of each item. For example, you have the data:
MyVar.1 = '08534 Joe Smith' MyVar.2 = '05137 Marg Wilson' MyVar.3 = '01388 Ed Johnson' MyVar.4 = '04111 Betty Brown'You wish to sort based upon the first 5 characters of each value. Now, you specify a length (ie, a positive sign, followed by a number) after the sort criteria letter. It would be as so:
SORT MyVar. A +5 DO i = 1 TO MyVar.0 SAY "MyVar." || i "=" MyVar.i ENDThe SORT template (A +5) means start at the first character, and use the next 5 characters after that. The A indicates that whatever appears as the first 5 characters is the primary sort criteria.
The output would be:
MyVar.1 = 01388 Ed Johnson MyVar.2 = 04111 Betty Brown MyVar.3 = 05137 Marg Wilson MyVar.4 = 08534 Joe SmithYou can also specify a start offset. For example, let's say that you wish to sort on the last 2 digits of the 5 digit code. You would specify a start offset of 4 (ie, the first character is at an offset of 1), and a length of 2:
SORT MyVar. 4 A +2 DO i = 1 TO MyVar.0 SAY "MyVar." || i "=" MyVar.i ENDThe output would be:
MyVar.1 = 04111 Betty Brown MyVar.2 = 08534 Joe Smith MyVar.3 = 05137 Marg Wilson MyVar.4 = 01388 Ed Johnson
Ignoring part of a value
But using a SORT template allows so much more sophisticated sorts. Assume that you have the following data consisting of some peoples' names, where the last name is first, and then the first name -- each separated by a blank space:
MyVar.1 = 'Smith Joe' MyVar.2 = 'Wilson Marg' MyVar.3 = 'Johnson Ed' MyVar.4 = 'Brown Betty'You want to sort according to the last names. Note that the length of each last name can be different. If you were a using a SORT that was limited to specifying only offset/length, then your data would have to be padded out with spaces (to the longest name) for the sort to work, like this:
MyVar.1 = 'Smith Joe' MyVar.2 = 'Wilson Marg' MyVar.3 = 'Johnson Ed' MyVar.4 = 'Brown Betty' SORT MyVar. A +7And of course, you'd have to know the length of the longest name before you could do the sort. But not so with SORT's dot placeholder. You can add it to the end of the template, as so:
SORT MyVar. A .Now SORT will break off the first space-delimited token (ie, word) of each value, and use that as the primary sort criteria. Everything else will be thrown away due to the trailing dot.
Multiple sort criteria
Consider this data:
MyVar.1 = 'Joe Smith' MyVar.2 = 'Peter Morgan' MyVar.3 = 'Ed Johnson' MyVar.4 = 'Phillip Morgan'You wish to sort by both last and first names, with the last name having preference. So, you're actually going to specify two sort criteria - a primary criteria (sort by last name) and a secondary criteria (after the last names are sorted, then do a sub-sort on the first names). You must use A to denote the primary sort criteria and B for the secondary sort criteria:
SORT MyVar. B ANow, each value is broken up into 2 space-delimited pieces. For example, MyVar.1's value would be broken up into "Joe" and "Smith". "Joe" would be the secondary sort criteria (because B appears in the SORT template first), and "Smith" would be the primary sort criteria (because A appears last).
And the results of the sort would be as if you originally did the following:
MyVar.1 = 'Ed Johnson' MyVar.2 = 'Peter Morgan' MyVar.3 = 'Phillip Morgan' MyVar.4 = 'Joe Smith'Note: Due to the convention of naming sort criteria from A to Z, there is a limit of 26 sort criteria per SORT statement.
Search strings
SORT's template allows even more sophisticated sorts. Let's consider that you have the following data consisting of some peoples' names and cities, where the last name is first, then a comma, then the first name, then another comma, and then the city. Because a city name can have a space, you have chosen to separate your fields with a comma instead of just a space, as so:
MyVar.1 = 'Smith, Joe, Atlanta' MyVar.2 = 'Wilson, Marg, New York' MyVar.3 = 'Johnson, Ed, Atlanta' MyVar.4 = 'Brown, Betty, Chicago'Now you wish to sort by city. SORT allows you to use "search strings" (ie, some text pattern to match) when isolating your sort criteria. Here's what you do:
SORT MyVar. . ',' . ',' AThe SORT template is a dot, followed by a quoted comma, another dot, another quoted comma, and finally the primary sort criteria (A). This means throw away everything before the first comma (due to the dot), throw away everything before the second comma, and use the rest of the value (after the second comma) as the sort criteria. Note that the commas are discarded too. (ie, The "search pattern" is also pulled out of the value before it is sorted). It's as if you're sorting an array that was assigned the following values:
MyVar.1 = ' Atlanta' MyVar.2 = ' New York' MyVar.3 = ' Atlanta' MyVar.4 = ' Chicago'And the results of the sort would be as if you originally did the following:
MyVar.1 = 'Johnson, Ed, Atlanta' MyVar.3 = 'Smith, Joe, Atlanta' MyVar.4 = 'Brown, Betty, Chicago' MyVar.2 = 'Wilson, Marg, New York'Note: A search string can be any amount of text (ie, not just a single character such as a comma).
Now suppose that you want to sort not only by city, but also alphabetically by last name. Now you need two sort criterias. You use "A" for the primary sort criteria and "B" for the secondary sort. Here's the SORT statement:
SORT MyVar. B ',' . ',' AThe SORT template is a B, a quoted comma, a dot, another quoted comma, and an A. This means use everything before the first comma as the secondary sort criteria (B), throw away everything between the first and second commas, and use the rest of the value as the primary sort criteria (A). It's as if you're sorting an array that was assigned the following values:
MyVar.1 = 'Smith Atlanta' MyVar.2 = 'Wilson New York' MyVar.3 = 'Johnson Atlanta' MyVar.4 = 'Brown Chicago'And there are two sorts done. First a sort is done on the city, and then a secondary sub-sort is done on the last name. The result would be as if you had originally assigned:
MyVar.1 = 'Johnson, Ed, Atlanta' MyVar.3 = 'Smith, Joe, Atlanta' MyVar.4 = 'Brown, Betty, Chicago' MyVar.2 = 'Wilson, Marg, New York'The array is sorted by both city name, and last name of the person, with city name taking preference over last name.
Using a variable to supply the search string
You can also use the value of a variable to contain the search string. You put the name of the variable in parentheses where you would otherwise put the search string. Here we do the same sort as above, but use a variable to supply the search string consisting of a comma:
breakoff = "," SORT MyVar. B (breakoff) . (breakoff) ANote: It's recommended you do not use a variable (to supply the search string) which is a tail of the same stem that you're sorting.
Descending sort order
For a descending order, use SORT DOWN, as so:
SORT DOWN MyVar.After the sort, the highest value will be the variable named (Stem).1 (instead of the lowest value).
Case-insensitive sorting/searching
The SORT statement normally is case-sensitive in its search strings, and its comparison of values. For example, if you use a search string of "hello", then it will not match "HELLO", "Hello", or any other differences in case. Also, if you have two variables whose values are "hello" and "HELLO" respectively, then the latter is considered "lower" than the former (ie, they are not equal values, due to case-sensitivity).
For case-insensitive matching of "search strings", and case-insensitive compare of values, use SORT CASELESS as so:
SORT CASELESS MyVar.You can combine DOWN and CASELESS (but CASELESS must come after DOWN):
SORT DOWN CASELESS MyVar.
Variables that fail the search criteria
If a particular variable fails to meet the sort criteria, then it is stuck on the end of the listing and not counted in the sort count. Reasons for why a variable may fail the sort criteria is if it doesn't contain all of the "search strings" specified in the template, or its length is not long enough to accomodate a start offset. For example, consider the following:
MyVar.1 = "*** 08534 Joe Smith" MyVar.2 = "*** 05137 Marg Wilson" MyVar.3 = "***" MyVar.4 = "*** 04111 Betty Brown" SORT MyVar. 5 a +5 DO i = 1 TO MyVar.0 SAY "MyVar." || i "=" MyVar.i ENDThis would produce the following output:
MyVar.1 = "*** 04111 Betty Brown" MyVar.2 = "*** 05137 Marg Wilson" MyVar.3 = "*** 08534 Joe Smith"The value of "***" that was previously assigned to MyVar.3 has been reassigned to MyVar.4 (because that value wasn't at over 5 characters long and therefore could not be evaluated at a start offset of 5). And the sort count does not reflect this extra data at the end of the array (ie, MyVar.0 = 3).
Re-sorting a stem
If you have sorted a particular stem and then wish to sort it again with a different criteria, note that any extra data appended to the array in a previous sort will be considered in the new sort.
Syncing stem variables
Assume that you have a stem variable "Name." that contains a list of names. You have a second stem variable "Phone." that contains the phone numbers of those people. The two arrays are ordered similiarly. For example, for the name stored in Name.1, the corresponding phone number is stored in Phone.1. The stem variables may be initialized as so:
Name.1 = "Smith" Name.2 = "Wilson" Name.3 = "Johnson" Name.4 = "Brown" Phone.1 = "Smith's phone #" Phone.2 = "Wilson's phone #" Phone.3 = "Johnson's phone #" Phone.4 = "Brown's phone #"Perhaps you would like to sort the Name. stem in alphabetical order. You then need to sort the Phone. stem so that the phone numbers are reordered similiarly. You can't use 2 SORT instructions because there's no criteria that you could supply to say "Re-order the Phone. stem in sync with the Name. stem".
For this reason, there is a "SORT" option which allows you to set a variable named Sort that contains the names of all stems that should be reordered in sync -- each stem name separated by a space. Here is how we may sort the Name. stem in alphabetical order, and reorder the Phone. stem respectively:
OPTIONS "SORT" Sort = "Phone." SORT Name.
Errors
If the SORT statement doesn't have enough memory to perform its operation, it may raise SYNTAX (5). It may also raise SYNTAX if you supply offsets and lengths that aren't numeric. Finally, it may raise NOVALUE if you use some variable to supply a search string, but have failed to initialize that variable.