Browsing and Updating with SAS/FSP

If your site has SAS/FSP software in addition to SAS/ACCESS software, you can browse and update SYSTEM 2000 data that is described by a view descriptor from within a SAS program. You might use one of three SAS/FSP procedures: FSBROWSE, FSEDIT, and FSVIEW. The FSBROWSE and FSEDIT procedures display one observation at a time. The FSVIEW procedure displays multiple observations in a tabular format (similar to the PRINT procedure). PROC FSVIEW enables you to both browse and update SYSTEM 2000 data, depending on which option you specify.

When browsing SYSTEM 2000 data using the FSVIEW procedure, remember that some values are repeated for each value of the variable: LASTNAME, FORENAME, POSITION, and PAYRATE. However, the value DAVID G. REID is stored in the database only one time. For retrievals, the results are straightforward. When updating data remember that values at higher levels in the database usually do not exist as often as they seem to. If you are using PROC FSVIEW and need to make a change in one of the values. For example, to change Adkins to Atkins, type the new information over one occurrence of the value that you want to change. With a single update operation, all matching values are corrected.

If you are using PROC FSEDIT and you want to delete an observation for David Reid remember that each observation for his positions and salary data also display his last name and first name. If you delete the observation for Assistant Programmer, the deletion would not affect the LASTNAME and FORENAME values, but the POSITION and PAYRATE values would be physically removed.

The FSBROWSE procedure enables you to look at SYSTEM 2000 data but not to change it. To use PROC FSBROWSE, submit the following:

    proc fsbrowse data=vlib.emppos;
    run; 

PROC FSBROWSE retrieves one observation at a time from a SYSTEM 2000 database. FSBROWSE Window shows the first observation of an employee's data described by the view descriptor Vlib.Emppos. (The view descriptor contains a SYSTEM 2000 ordering-clause to order the data by last name, which is missing in the first observation. That is, an employee has not yet been hired for that position.) To browse each observation, use the FORWARD or BACKWARD command.

The FSEDIT procedure enables you to update SYSTEM 2000 data described by a view descriptor, if you have been granted the appropriate SYSTEM 2000 update authorities. For example, in the previous FSBROWSE window, the LASTNAME and FIRSTNME values are missing in the first observation. You can add values to these items by using PROC FSEDIT.

    proc fsedit data=vlib.emppos;
    run;

PROC FSEDIT retrieves one observation at a time. To edit data in the window, type your information. For example, for this observation, type the value 'Adkins' for LASTNAME and 'Mary' for FIRSTNME.

To end your editing session, issue the END command. To cancel an edit, you must issue the CANCEL command before you scroll to another observation. After you scroll, the change is incorporated.

The FSVIEW procedure enables you to browse or update SYSTEM 2000 data using a view descriptor, depending on how you invoke the procedure.

To browse SYSTEM 2000 data in a listing format, submit the following:

    proc fsview data=vlib.emppos;
    run;

Browse mode is the default for PROC FSVIEW. In the FSVIEW window title in Figure 5.3, notice the (B) that follows the view descriptor's name, which indicates browse mode. Also notice that the name Mary Adkins appears, reflecting the update that you made by using PROC FSEDIT.

To edit SYSTEM 2000 data in a listing format, you must add the MODIFY option to the PROC FSVIEW statement, as follows:

    proc fsview data=vlib.emppos modify;
    run; 

The same window as shown in FSBROWSE window appears, except the window title NOW contains an (E), which indicates edit mode. For information about editing data using the FSVIEW procedure, see SAS/FSP 9.3 Procedures Guide.

You can use a WHERE statement with the SAS/FSP procedure statements to specify conditions that subset the retrieved SYSTEM 2000 data. After you have invoked one of the SAS/FSP procedures, you can use a WHERE command to subset retrieved SYSTEM 2000 data.

It is more efficient to use a WHERE clause rather than a subsetting IF statement. The interface view engine translates a WHERE clause into SYSTEM 2000 conditions and passes the conditions to SYSTEM 2000 software, connecting them by default using a Boolean AND, to any SYSTEM 2000 where-clause included in the view descriptor. A where-clause in SYSTEM 2000 can reference items contained in a view descriptor and items contained in the access descriptor that the view descriptor is based on. Unlike a where-clause in SYSTEM 2000 that is stored in a view descriptor, a WHERE clause in SAS is restricted to items contained in the view descriptor.

Whether using a WHERE clause in SAS or a where-clause in SYSTEM 2000, specifying selection criteria works essentially like filters. That is, more data goes into the clauses than comes out. Using the SAS/ACCESS interface, you can pass data through more than one filter. Each filter does part of the subsetting. This is called successive filtering.

Sometimes, the interface view engine cannot translate all the WHERE clause conditions in SAS into SYSTEM 2000 conditions. In such cases, the engine subsets what it can. As partially filtered records are passed back to SAS, SAS automatically re-applies the entire WHERE clause as a second filter. This is called post-processing. For more information, seeWHERE Clauses in SAS and where-clause in SYSTEM 2000.

In some SAS procedures, such as PROC FSEDIT, you can continue to apply more filters by using the WHERE command on the command line in SAS. Each time you enter another WHERE clause, the process of combining and filtering conditions is repeated. The interface engine decides what conditions it can handle, connects them by default to the prior conditions by using the Boolean operator AND, sends them to SYSTEM 2000 for the first (sometimes only) filtering, and then tells SAS to do any final filtering as required. For more information, see WHERE Clauses in SAS and where-clause in SYSTEM 2000.

In the following example, the subset of retrieved employee data comes from the Corporation Department, that is, the executives. Figure 5.4 shows the FSEDIT window after you submit the following program. Notice that the word (Subset) appears in the window title to indicate that the data that is retrieved is a subset of the data that is described by the view descriptor. Eleven observations that have the value CORPORATION for DEPARTME are retrieved for editing.

    proc fsedit data=vlib.emppos;
       where departme='CORPORATION';
    run; 

If you subset the data from within the procedure with the following command,

    where departme='CORPORATION'

the results would be identical except that the window title would show WHERE ..., instead of (Subset), to indicate that a filter had been applied.

Although these examples have shown how to use a WHERE clause with PROC FSEDIT, you can also use WHERE clauses with PROC FSBROWSE and PROC FSVIEW. For more information about the WHERE statement in SAS, see SAS 9.3 Statements: Reference and SAS Language Reference: Concepts . For more information about the WHERE command in SAS/FSP procedures, see the SAS/FSP 9.3 Procedures Guide

When you insert or delete data in a SYSTEM 2000 database by using a SAS/FSP procedure, be aware that the updates have the potential of affecting more than one data record in the database.

If you insert a new observation, it can cause more than one SYSTEM 2000 data record to be inserted based on how many levels the new observation represents and on a comparison between the data being inserted and the data in the last observation read, if any. During an insert, levels having data that is different from the prior observation, if any, cause a data record to be inserted. Based on how many fields you change, one or more records are inserted at the levels that have changed. If your application inserts records in a random fashion (for example, you want to add a position record for one employee while looking at the data for another employee) you should specify a BYKEY in your view descriptor. For more information about inserting data records and using a BYKEY to resolve ambiguous inserts, see Advanced Topics for Users.

If you delete an observation, the results are not obvious to you and might be difficult to predict. The interface view engine must handle deletes carefully to ensure that the data that you request to be deleted does not adversely affect another user of the database. When you issue the DELETE command, you can expect one of the following results:

The following example shows how to edit the SYSTEM 2000 data by deleting an observation, which is described by Vlib.Emppos. If you have been granted update authority, you can use the PROC FSEDIT statement, scroll forward to the observation that you want to delete, and issue the DELETE command from the command line, as shown in Figure 5.5.

The DELETE command processes the deletion and displays a message as shown in Figure 5.6. The observation that you deleted is no longer available for processing.

Even though it looks as if the entire observation is removed from the database, the records are not physically removed because the POSITION WITHIN COMPANY record has descendant records that would be affected by removal. The interface view engine sets the values for the lowest-level items (POSITION, DEPARTME, and MANAGER) to missing; the values for LASTNAME and FORENAME are not affected because they are at level 0 and have descendant records. Also, values for other items in the POSITION WITHIN COMPANY record are not affected.

For more information about using the SAS/FSP procedures, see the SAS/FSP Procedures Guide.