|SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference|
If your site has SAS/FSP software as well as SAS/ACCESS software, you can browse and update SYSTEM 2000 data described by a view descriptor from within a SAS program. You have a choice of three SAS/FSP procedures: FSBROWSE, FSEDIT, and FSVIEW. The FSBROWSE and FSEDIT procedures show you one observation at a time. The FSVIEW procedure displays multiple observations in a tabular format (somewhat similar to the PRINT procedure) where you can see many observations at once. PROC FSVIEW enables you to both browse and update SYSTEM 2000 data, depending on which option you choose.
You cannot use the FSBROWSE, FSEDIT, or FSVIEW procedures on an access descriptor.
To scroll through the data from within a SAS/FSP window, use the FORWARD and BACKWARD commands. To end your browse or edit session, issue the END command.
|Using the FSBROWSE Procedure|
The FSBROWSE procedure enables you to look at SYSTEM 2000 data but not to change them. To use PROC FSBROWSE, submit the following SAS code:
proc fsbrowse data=vlib.emppos; run;
The FSBROWSE procedure retrieves one observation from a SYSTEM 2000 database at a time. The following display shows the first observation of an employee's data described by the VLIB.EMPPOS view descriptor. (The view descriptor contains a SYSTEM 2000 ordering-clause to order the data by last name, which is missing for the first observation; that is, an employee has not yet been hired for the position.) To browse each observation, use the FORWARD and BACKWARD commands.
|Using the FSEDIT Procedure|
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 display, 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 like PROC FSBROWSE. To edit data in the display, simply enter your changes. For example, for this observation, enter the value 'Adkins' for LASTNAME and 'Mary' for FIRSTNME.
To end your editing session, issue the END command. If you wanted to cancel an edit, you would issue the CANCEL command, but you must do this before you scroll to another observation. Once you scroll, the change is committed.
|Using the FSVIEW Procedure|
The FSVIEW procedure also enables you to browse or update
SYSTEM 2000 data using a view descriptor, depending on how you invoke the
To browse SYSTEM 2000 data in a listing format, submit the code:
proc fsview data=vlib.emppos; run;
Browse mode is the default for PROC FSVIEW. Notice that a (B) for browse follows the view descriptor's name. Also notice that the name Mary Adkins appears, reflecting the update you made using the FSEDIT procedure in the previous example.
To edit SYSTEM 2000 data in a listing format, you must add the MODIFY option to the PROC FSVIEW statement, as shown in the following code:
proc fsview data=vlib.emppos modify; run;
The same window as shown in the previous display appears, except the window title contains an (E), not a (B). SAS/FSP Software: Usage and Reference discusses in detail how to edit data using the FSVIEW procedure. Note that the CANCEL command in the FSVIEW window does not cancel your changes, whether you have scrolled or not.
|Specifying a WHERE Clause While Browsing or Editing|
You can specify a SAS WHERE statement with the SAS/FSP procedure statements to specify conditions that subset the retrieved SYSTEM 2000 data. You can also use a SAS WHERE command to do the same thing after you have invoked one of the SAS/FSP procedures.
Keep in mind that 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. Unlike a SYSTEM 2000 where-clause stored in a view descriptor, however, a SAS WHERE clause is restricted to items contained in the view descriptor. (A SYSTEM 2000 where-clause can reference items contained in a view descriptor and items contained in the access descriptor that the view descriptor is based on.)
Specifying selection criteria, whether in the form of a SAS WHERE clause or a SYSTEM 2000 where-clause, 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, with each filter doing part of the subsetting. This is called successive filtering.
Sometimes, the interface view engine cannot translate all the SAS WHERE clause conditions into SYSTEM 2000 conditions. In such cases, the engine subsets what it can. As partially-filtered records are passed back to the SAS System, the SAS System automatically reapplies the entire WHERE clause as a second filter. (This is referred to as post-processing.) See Using a SAS WHERE Clause for Selection Criteria for more information on what SAS WHERE clause conditions can and cannot be translated.
In some procedures, such as the FSEDIT procedure, you can continue to apply more filters by using the SAS WHERE command on the command line. Each time you enter another clause, the process of combining and filtering conditions is repeated. The engine decides what conditions it can handle, connects them by default with an AND to the prior conditions, sends them to SYSTEM 2000 software for the first (sometimes only) filtering, and then tells the SAS System to do any final filtering as required. For more information on using a SAS WHERE clause with a view descriptor, see Using a SAS WHERE Clause for Selection Criteria.
In the following example, the subset of retrieved employees are those from the Corporation Department, that is, the executives. An example of the FSEDIT window after the code has been submitted follows.
proc fsedit data=vlib.emppos; where departme='CORPORATION'; run;
Eleven observations with a DEPARTME value of CORPORATION are retrieved for editing. Note that the word (Subset) appears after VLIB.EMPPOS in the window title to remind you that the data retrieved are a subset of the data described by the view descriptor.
Had you subset the data from within the procedure with the command:
the results would be identical except the window title would say WHERE ... instead of (Subset) to indicate that a filter had been applied.
Although these examples have shown establishing a WHERE clause with the FSEDIT procedure, you can also establish WHERE clauses when using the FSBROWSE and FSVIEW procedures. For more information on the SAS WHERE statement, see SAS Language Reference: Dictionary and SAS Language Reference: Concepts. For more information on the WHERE command within the SAS/FSP procedures, refer to SAS/FSP Software: Usage and Reference.
|Inserting and Deleting Data Records|
When you do an insertion or a deletion for a SYSTEM 2000 database using a SAS/FSP procedure, you must be aware that the updates have the potential of affecting more than one data record in the database.
When you insert a new observation, it can cause the insertion of more than one SYSTEM 2000 data record, depending on how many levels the new observation represents and depending on a comparison between the data being inserted and the data in the last observation read, if any. During an insert, levels having data different from the prior observation, if any, result in a data record insertion. Depending 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 to one employee while looking at another employee, then you should specify a BY key in your view descriptor. See Advanced User Topics for more information on inserting data records and on using a BY key to resolve ambiguous inserts.
When you delete an observation, the results are not obvious to you and may be difficult to predict. The interface view engine must handle deletes carefully to ensure that what you request to delete 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 illustrates using the DELETE command in the FSEDIT procedure. Suppose you want to edit the SYSTEM 2000 data described by VLIB.EMPPOS to delete the following observation. If you have been granted update authority, you can use the PROC FSEDIT statement. Scroll forward to the observation you want to delete, and issue the DELETE command on the command line, as shown in the following display.
The DELETE command processes the deletion and displays a message to that affect, as shown in the following display. There is no indication to you of what actions the interface view engine takes. Regardless of the actions, though, the observation you delete is no longer available for processing.
Even though it looks as if the entire observation is removed from the database, this is not the case in this example. The interface view engine sets the values for the bottom level items (POSITION, DEPARTME, and MANAGER) to missing; the records are not physically removed because the POSITION WITHIN COMPANY RECORD has descendant records that would be affected by a removal. The values for LASTNAME and FORENAME are not affected because they are at level 0 with descendant records. Also, values for other items in the POSITION WITHIN COMPANY record are not affected.
For more information on using the SAS/FSP procedures, see SAS/FSP Software: Usage and Reference.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.