Merging Data

How to:

You can merge two or more data sources, and specify which records to merge and which to sort out, using the MATCH command. The command creates a new data source (a HOLD file), into which it merges fields from the selected records. You can report from the new data source and use it as you would use any other HOLD file.

You select the records to be merged into the new data source by specifying sort fields in the MATCH command. You specify one set of sort fields (using the BY phrase), for the first data source, and a second set of sort fields for the second data source. The MATCH command compares all sort fields that have been specified in common for both data sources, and then merges all records from the first data source whose sort values match those in the second data source into the new HOLD file. You can specify up to 128 sort sets. This includes the number of common sort fields.

In addition to merging data source records that share values, you can merge records based on other relationships. For example, you can merge all records in each data source whose sort values are not matched in the other data source. Yet another type of merge combines all records from the first data source with any matching records from the second data source.

You can merge up to 16 sets of data in one Match request. For example, you can merge different data sources, or data from the same data source.

Note: The limit of 16 applies to the most complex request. Simpler requests may be able to merge more data sources.

Syntax: How to Merge Data Sources

The syntax of the MATCH command is similar to that of the TABLE command:

MATCH FILE file1 
.
.
.
RUN 
FILE file2 
.
.
.
[AFTER MATCH merge_phrase]
RUN 
FILE file3 
.
.
.
[AFTER MATCH merge_phrase]
END

where:

file1

Is the first data source from which MATCH retrieves requested records.

merge_phrase

Specifies how the retrieved records from the files are to be compared. For details, see Merge Phrases.

file2/file3

Are additional data sources from which MATCH retrieves requested records.

Note that a RUN command must follow each AFTER MATCH command (except for the last one). The END command must follow the final AFTER MATCH command.

MATCH generates a HOLD file. You can print the contents of the HOLD file using the PRINT command with the wildcard character (*).