HPlogo SORT-MERGE/XL General User's Guide > Chapter 2 Getting Started With SORT-MERGE/XL

Key Data Items

MPE documents

Complete PDF
Table of Contents
Index
Glossary

In SORT-MERGE/XL a key data item is a group of alphabetic, numeric, or alphanumeric characters. The key data item is used by SORT-MERGE/XL as a reference to find and arrange the data in a specified order. You specify a key data item by identifying its position (column) in the record and its length (number of succeeding columns) with the >KEY command.

A record is a continuous collection of related data that is treated as one unit. A record can consist of more than one line of data in a file. It is continued to subsequent lines by entering an ampersand (&) as the last nonblank character on a line.

To define a key data item with the >KEY command, enter: 

  >KEY 40, 12
This specifies a key data item that begins in the 40th character position (column) of the record and is 12 characters (columns) long.

You can sort or merge data based on one or more key data items.

Sorting Files By A Single Key Data Item

SORT-MERGE/XL can sort or merge files based on a single key data item within a record. Figure 2-1 "Key Data Item Positions" shows three records of data in a file. Each record is one line in length. Each record contains a person's last name, first name, occupation, and social security number. The last name starts in column 1, the first name in column 10, the occupation in column 25, and the social security number in column 40.

NOTE: The examples in this chapter contain two extra lines of numbers (for example, 123456...9). These two extra lines are included to show that the data is aligned in the columns established as key data items. These two extra lines will not appear in your file.

Figure 2-1 Key Data Item Positions

[Key Data Item Positions]

To define the last name as the single key data item to be sorted alphabetically, enter: 

  >KEY 1, 9
The result of a sort done on the records shown in Figure 2-1 "Key Data Item Positions" based on the command >KEY 1, 9, is shown in Figure 2-2 "Alphabetical Sort By Last Name" Note that the last names are now arranged in alphabetical order.

Figure 2-2 Alphabetical Sort By Last Name

[Alphabetical Sort By Last Name]

To define the social security number as the single key data item to be sorted numerically, enter: 

  >KEY 40, 9
The result of a sort done on the records shown in Figure 2-1 "Key Data Item Positions" based on the command >KEY 40, 9, is shown in Figure 2-3 "Numerical Sort By Social Security Number" Note that the social security numbers are now arranged in ascending numerical order.

Figure 2-3 Numerical Sort By Social Security Number

[Numerical Sort By Social Security Number]

NOTE: All entries in a file to be sorted for a key data item must start in exactly the same column. In Figure 2-1 "Key Data Item Positions" all last names start in column 1 and the number of characters must not extend into the next data item field. Therefore, if Boris Tscherbakhanovski were added to the list of last names in Figure 2-1 "Key Data Item Positions" his last name would have to be shortened to Tscherbak so it would not extend into the next key data item field containing first names. If you want to merge this file with other files, the key data items in all files must be located in exactly the same position and have the same data format.

Sorting Files By Multiple Key Data Items

SORT-MERGE/XL allows you to specify more than one key within a record for sort or merge purposes. For example, the data in Figure 2-1 "Key Data Item Positions" can be arranged according to four different key data items (last name, first name, occupation, or social security number). The command to specify the last name as the single key data item to be sorted is: 

  >KEY 1, 9
This specifies that the key data item to be sorted begins in character position (column) 1 and is 9 characters (columns) long. The result of a sort done using the command >KEY 1, 9 is shown in Figure 2-2 "Alphabetical Sort By Last Name".

The command to specify a multiple key data item sort with the last name as the major key data item, and the first name as the second key data item, is: 

  >KEY 1, 9; 10, 14
This specifies that the major key data item to be sorted is located in character position (column) 1 and is 9 characters long; the second key data item begins at character position 10 and is 14 characters long.

If there were two identical last names, the sort program would look to the second key data item to break the tie. Multiple >KEY commands may be entered one to a line, or all on one line, each separated by a semicolon: 

  >KEY 1, 9
  >KEY 10, 14
  >KEY 25, 15
Or 

  >KEY 1, 9; 10, 14; 25, 15
If you define multiple key data items with the >KEY command, the priority of the sort operation is:

  • SORT-MERGE/XL treats the first key data item you enter (in this example the last name) as the major key and sorts that item first.

  • If there are two or more items of equal value in the major key (two identical last names), the key data items are ordered according to the second data item identified with the >KEY command.

  • In the case of ties on the second data item, the third data item entered with the >KEY command is used, and so on.

  • If two or more records are equal in all key fields, the original order of the records in the input file(s) is used. (This is not possible in this example since each person has a unique social security number.)

For additional information on sorting by single or multiple key data items, refer to Chapter 3.

Merging Files By Key Data Items

MERGE/XL allows you to combine two or more sorted files into a single, new file based on one or more key data items. Figure 2-4 "Sorted File SORTED1" shows a file called SORTED1. SORTED1 contains the three records shown as sorted in Figure 2-2 "Alphabetical Sort By Last Name"

Figure 2-4 Sorted File SORTED1

[Sorted File SORTED1]

You can merge this data with one or more additional sorted files such as SORTED2, shown below in Figure 2-5 "Sorted File SORTED2"

Figure 2-5 Sorted File SORTED2

[Sorted File SORTED2]

If you merge the two files (SORTED1 and SORTED2) based on the command >KEY 1, 9; 10, 14, the resulting new file (MERGED1) would contain the information shown in Figure 2-6 "Merged File MERGED1"

Figure 2-6 Merged File MERGED1

[Merged File MERGED1]

For additional information on merging files, refer to Chapter 4.



SORT- MERGE/XL Commands

Collating Sequences