Advanced Topics

Deleting and Inserting Data Records in CA-Datacom/DB

Deleting and inserting data records in a CA-Datacom/DB table is a straightforward process for SAS as well as for CA-Datacom/DB. However, several considerations are worth noting.

When inserting a new row in SAS, you normally visualize the new data being appended at the end of the SAS data file. However, CA-Datacom/DB conventions affect the appearance of a procedure such as FSEDIT.

When you include a WHERE clause or criteria that require a Select file, the customary SAS behavior applies. Otherwise, newly added data records appear in order by the Native Key, not at the end. The new records are always inserted in the table in the proper place, in order by the Native Key. Including or not including a WHERE clause simply determines how the table looks to SAS.

Note: If you are going to use a view descriptor to insert new data records, you must select at least one key (or the fields that make up the key) for that view descriptor. Select the Master Key if the Master Key values must be unique. If you request repositioning (that is, the DDBLOAD= option is set to zero or default to zero), either the default key must be selected or all of the fields that it comprises must be selected. An ADD cannot be processed if DDBLOAD equals 0 and the Default Key field is set to blanks.
If INCLUDE-NIL-KEY equals YES, null values are indexed. However, if DUPE-MASTER-KEY equals NO, duplicate values cannot be indexed. That is, if you insert two records with missing values for the Master Key, the second insert fails. Native Key values must be unique, another reason that the second insert of a missing value fails.
In order for you to even see newly added records while in an interactive procedure, the interface view engine does some fairly complex processing that is referred to as repositioning. Repositioning means retrieving a record immediately after it has been inserted, so that it is visible to you. This is important in an interactive procedure, but not as important in a procedure such as PROC APPEND. Therefore, to save processing time, a DDBLOAD= data set option is available that enables you to turn repositioning on and off. Setting the DDBLOAD= data set option causes the APPEND procedure, for example, to process faster and more efficiently. For more information about the DDBLOAD= data set option, see Data Set Options.
Deleting a data record removes the record from the CA-Datacom/DB table. The deleted data record is no longer available for access even though it might have satisfied the WHERE clause selection criteria before it was removed.

Top of Page