Using SAS Tables

Updating SAS Tables in SCL Programs

When a table row is read, its data follow a path from the SAS table through the TDV to the SDV, where finally they can be manipulated. After the data is manipulated, it must follow the reverse path from the SDV through the TDV back to the SAS table. If you use the SET routine to link the values from the TDV to the SDV, then any changed values are automatically linked from the SDV back to the TDV. If you do not use SET, then you must explicitly copy the value of each variable to the TDV. In either case, you use the UPDATE function to copy the values from the TDV to the SAS table.

Appending Rows

To add new rows to a SAS table rather than updating the existing rows, use the APPEND function. If the SCL variables have the same name and type as the SAS table columns and you use the SET routine to link them, then using the APPEND function is straightforward, and the values are automatically written from the TDV to the SAS table.

Note: If the program does not use the SET routine, or if the APPEND function is used with the NOSET option, a blank row is appended to the SAS table. This is a useful technique for appending rows when the SCL program or the window variables do not match the SAS table columns. For example, when the SET routine is not used, you would use a sequence of statements like those below to append a blank row and then update it with values. [cautionend]

rc=append(dsid);
...PUTVARC or PUTVARN program statement(s)...
rc=update(dsid);

Deleting Rows

To delete rows from a SAS table, use the DELOBS function. In order to use this function, the SAS table must be open in UPDATE mode. The DELOBS function performs the following tasks:

marks the row for deletion from the SAS table. However, the row is still physically in the SAS table.
prevents any additional editing of the row. Once a row has been marked for deletion, it cannot be read.

Remaining Rows Not Renumbered

Although deleted rows are no longer accessible, all other rows in the SAS table retain their original physical row numbers. Therefore, it is important to remember that a row's physical number may not always coincide with its relative position in the SAS table. For example, the FETCHOBS function treats a row value as a relative row number. If row 2 is marked for deletion and you use FETCHOBS to read the third row, FETCHOBS reads the third non-deleted row--in this case, row 4. However, you can use FETCHOBS with the ABS option to count deleted rows.

Non-deleted rows are intentionally not renumbered so that you can continue to use row numbers as pointers. This is important when you are using the FSEDIT procedure or subsequent SAS statements that directly access table rows by number, such as the POINT= option in a SAS language SET statement.

You can control row renumbering if necessary. See the next section for details.

Renumbering Rows

To renumber accessible SAS table rows, an SCL program must use one of the following techniques to process the SAS table:

Sort the table, using either the SORT function in SCL or the SORT procedure. If the SAS table is already in sorted order, then you must use the FORCE option.

Note: The SORT function and PROC SORT do not sort and replace an indexed SAS table unless you specify the FORCE option, because sorting destroys indexes for a SAS table.
Copy the table, using either the COPY function in SCL or the COPY procedure. In this case, the input and output tables must be different. The output table is the only one that is renumbered.
Read the remaining data table rows, using the SAS language SET statement in a DATA step (not the SCL SET statement), and write these rows to a data table. To avoid exiting from SCL, you can use a submit block. For example:
```
houseid=open('sasuser.houses','u');
   ...SCL statements that read rows and delete rows...
submit continue;
   data sasuser.houses;
      set sasuser.houses;
   run;
endsubmit;
```

Top of Page