Advanced Topics

Using a BY Key to Resolve Ambiguous Inserts

When the interface view engine is called to examine additional ADABAS records in order to add a new periodic group occurrence, the engine must decide whether to add a new logical record or modify an existing one. The purpose is to reduce data redundancy.

You can help in the resolution of this decision by specifying a BY key. You can specify BY keys in the access descriptor by using the KEY statement. If ASSIGN NAMES=NO, you can use the KEY statement to specify BY keys in the view descriptor. Only elementary data fields that are designated as ADABAS descriptors can be specified as BY keys.

A BY key is a set of match variables. A data field is a good candidate for a BY key if it uniquely identifies a logical record.

A BY key is similar to a BY group in SAS, which groups observations based on one or more fields. Many SAS procedures process records in BY groups. Also, some updates in the DATA step are performed by matching specified BY variables in different data sets. A similar matching process occurs with BY key data fields in the SAS/ACCESS interface to ADABAS.

The BY key comparison process is as follows:

If values for a BY key match a record already in the ADABAS file, it will be modified. That is, the interface view engine inserts a new occurrence within a periodic group.
If values for a BY key do not match an existing record, a new record is added to the ADABAS file.

BY Key Examples

The following examples illustrate that using a BY key helps keep data organized and prevents unnecessary duplication of data.

Suppose you are working with the following two ADABAS logical records, which make up three SAS observations as shown in the following output. The data field named DF1 is specified as a BY key. DF2 is a periodic group consisting of data fields DF21 and DF22.

By Key Example Containing Two ADABAS Logical Records of Three SAS Observations

Data Fields        DF1            DF2
                                     DF21   DF22
Record 1           A                 CCC    1       (obs 1)
                                     CCC    2       (obs 2)
Record 2           B                 DDD    3       (obs 3)

By Key Example 1

You are in the FSEDIT procedure on observation 1. You enter an ADD or a DUP command and the values A, CCC, and 4. This is not an ambiguous insert, and a BY key is not required. The following output shows the result.

Results of Entering an ADD or DUP Command

Data Fields        DF1            DF2
                                     DF21   DF22
Record 1           A                 CCC    1       (obs 1)
                                     CCC    2       (obs 2)
                                     CCC    4       (new observation (obs 4))
Record 2           B                 DDD    3       (obs 3)

By Key Example 2

You are in the FSEDIT procedure on observation 1. You enter an ADD or a DUP command and the values B, DDD, and 5 for data fields DF1, DF21, and DF22, respectively. This is an ambiguous insert because all the values you are entering are different than the ones in observation 1. If there were not a BY key, the result would be as shown in the following output.

Results of an Ambiguous Insert

Data Fields        DF1            DF2
                                     DF21   DF22
Record 1           A                 CCC    1       (obs 1)
                                     CCC    2       (obs 2)
                                     CCC    4       (obs 3)
Record 2           B                 DDD    3       (obs 4)
Record 3           B                 DDD    5       (new observation)

With a BY key, the engine locates the BY key value DF1=B. The following output shows the result.

Results with a BY Key

Data Fields        DF1            DF2
                                     DF21   DF22
Record 1           A                 CCC    1       (obs 1)
                                     CCC    2       (obs 2)
                                     CCC    4       (obs 3)
Record 2           B                 DDD    3       (obs 4)
                                     DDD    5       (new observation)

By Key Example 3

You are in the FSVIEW procedure, looking at the first three observations. You decide to add the values B, DDD, and 7 at the end. The current position is the third observation on the display. The following output shows the result with no BY key.

Results without a By Key

Data Fields        DF1            DF2
                                     DF21   DF22
Record 1           A                 CCC    1       (obs 1)
                                     CCC    2       (obs 2)
                                     CCC    4       (obs 3)
Record 2           B                 DDD    3       (obs 4)
                                     DDD    5       (obs 5)
Record 3           B                 DDD    7       (new observation)

The following output shows the result with a BY key.

Results with a BY Key

Data Fields        DF1            DF2
                                     DF21   DF22
Record 1           A                 CCC    1       (obs 1)
                                     CCC    2       (obs 2)
                                     CCC    4       (obs 3)
Record 2           B                 DDD    3       (obs 4)
                                     DDD    5       (obs 5)
                                     DDD    7       (new observation)

BY Key Considerations

When specifying BY keys for your view descriptors, keep in mind the following considerations:

A duplicate consecutive observation results in an additional occurrence in any periodic group in the view descriptor.
If you do an insert from an observation that has all missing values, the interface view engine inserts a record that is equivalent to all zeros and blanks.
The APPEND function of the SAS Component Language (SCL) must be preceded by a call to the SET function. Otherwise, APPEND inserts an observation that is equivalent to all zeros and blanks because the insert is too ambiguous for the interface view engine to resolve.
If a view descriptor includes a periodic group and you try to add an observation that is another occurrence in that periodic group, the add might fail if you are attempting to add more occurrences than the periodic group field definition allows. One of the following will occur, depending on whether a BY key is specified:
- If no BY key is defined, and
  - if the last observation was not created from the periodic group, a new logical record is added.
  - if the last observation was created from the periodic group, the add fails with a return code, and a new record is then added.
- If a BY key is defined and the periodic group is selected to have an added occurrence, the add fails and a message displays.

Top of Page