|SAS/ACCESS Interface to CA-DATACOM/DB: Reference|
If your site has SAS/FSP software as well as SAS/ACCESS software, you can browse and update CA-DATACOM/DB 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 data record at a time, while the FSVIEW procedure displays multiple records in a tabular format similar to the PRINT procedure. PROC FSVIEW enables you to browse or update CA-DATACOM/DB 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, 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 CA-DATACOM/DB data but not to change it. To use PROC FSBROWSE, submit the following SAS statements:
proc fsbrowse data=vlib.usacust; run;
The FSBROWSE procedure retrieves one record at a time from a CA-DATACOM/DB table. FSBROWSE Window shows the first record of the USA customers' data described by the VLIB.USACUST view descriptor. To browse each record, use the FORWARD and BACKWARD commands.
|Using the FSEDIT Procedure|
The FSEDIT procedure enables you to update CA-DATACOM/DB data described by a view descriptor. For example, in FSBROWSE Window the ZIPCODE, NAME, and FIRSTORD values are missing in the first record. You can add values to these fields with the FSEDIT procedure.
To use PROC FSEDIT, submit the following SAS statements:
proc fsedit data=vlib.usacust; run;
The FSEDIT procedure also retrieves one
record at a
time. To edit a record, scroll to it, and type in the new data after the appropriate
label. For example, enter the information about the
DURHAM SCIENTIFIC SUPPLY COMPANY, as shown in FSEDIT Window.
To end your editing session, issue the END command.
Note: To cancel an edit, you must do so before you scroll.
The CANCEL command does not cancel your editing changes; it redisplays the
record as it was before you began to edit it. Once you scroll, the changes
Note: The data are presented in order by the Default
Key value (usually the Native Key) unless the view descriptor contains a SORT
|Using the FSVIEW Procedure|
on how you invoke the FSVIEW procedure, you can either browse or update data
using a view descriptor.
To browse CA-DATACOM/DB data, submit the PROC FSVIEW statement as follows:
proc fsview data=vlib.usacust; run;
PROC FSVIEW displays the data in a listing format instead of one observation at a time, as shown in FSVIEW Window. Browse mode is the default for the FSVIEW procedure. Notice that a (B) for browse follows the view descriptor's name and that the values in the first record reflect the changes made using the FSEDIT procedure in the previous example.
To see the rest of the table's data, scroll the display to the right several times by issuing the RIGHT command on the command line or by using the function key assigned to this command.
Note: The data are presented in order by the Default Key value (usually the Native
Key) unless the view descriptor contains a SORT clause. If the view descriptor
contains a WHERE clause but no SORT clause, the order is unpredictable.
To edit CA-DATACOM/DB data with PROC FSVIEW, submit the FSVIEW statement as follows:
proc fsview data=vlib.usacust modify; run;
The word "EDIT" can be used instead of MODIFY. The display will be the same as FSVIEW Window except that an (E) for edit will be displayed.
Note: Any update in the FSVIEW window is final. The
CANCEL command in the FSVIEW window does not cancel your changes,
whether or not you have scrolled.
|Specifying a WHERE Clause While Browsing or Editing|
You can specify a WHERE statement to subset CA-DATACOM/DB data when you invoke the SAS/FSP procedures. You can also use a WHERE command to do the same thing after you have invoked one of the SAS/FSP procedures.
In the following example, a WHERE statement is used to retrieve only customers from California. FSEDIT Window After SAS WHERE Statement shows the FSEDIT window after the statements have been submitted.
proc fsedit data=vlib.usacust; where state='CA'; run;
FSEDIT Window After SAS WHERE Statement
Only two records with a STATE value of CA are retrieved for editing. Note that the word (Subset) appears after VLIB.USACUST in the window title to remind you that the data retrieved are a subset of the data described by the view descriptor. You can then edit each record by typing over the information you want to modify. Issue the END command to end your editing session. If you want to cancel changes to a record, you can issue the CANCEL command before you scroll. Once you scroll though, the change is committed.
You can also use a SAS WHERE command to display a subset of your data. A WHERE command is a SAS WHERE expression that is entered on the command line. FSEDIT Window After SAS Window After SAS WHERE Command shows how the FSEDIT display appears when the subset is generated within the procedure with the following WHERE command:
FSEDIT Window After SAS Window After SAS WHERE Command
Only the two records with a STATE value of CA are retrieved for editing. Where... appears after VLIB.USACUST in the window title to remind you that the data retrieved are a subset of the data described by the view descriptor. You can then edit each record, as described earlier.
Although these examples have shown a WHERE clause with the FSEDIT procedure, you can also retrieve a subset of the data when using the FSBROWSE and FSVIEW procedures. For more information on the SAS WHERE statement, see the SAS Language Reference: Dictionary. For more information on the SAS WHERE command within the SAS/FSP procedures, refer to the SAS/FSP Software: Usage and Reference.
|Inserting and Deleting Data Records with the SAS/FSP Procedures|
Inserting and deleting records with the SAS/FSP procedures is different for view descriptors than for SAS data files.
You can use the FSEDIT and FSVIEW procedures to insert records into a CA-DATACOM/DB table on which a view descriptor is based. Insertion of new records also depends on the attributes assigned to the Master Key and whether the Master Key is included in your view descriptor. For example, if the DUPE-MASTER-KEY attribute is set to N (no), values for the Master Key cannot be duplicated. You will receive an error message if you try to insert a record having a Master Key value that duplicates an existing value. Therefore, be sure to define your view descriptors carefully if you intend to use them to insert records into a CA-DATACOM/DB table.
Refer to Information for the Database Administrator for details on inserting data. Refer to SAS/FSP Software: Usage and Reference for information on how to use insertion commands such as ADD and DUP in the FSEDIT procedure and AUTOADD and DUP in the FSVIEW procedure. However, note that with the SAS/ACCESS interface to CA-DATACOM/DB, a duplicated record is inserted immediately after the original record rather than being added at the end of the CA-DATACOM/DB table.
When you use the DELETE command with a view descriptor that describes CA-DATACOM/DB data, the current record is removed permanently from the CA-DATACOM/DB table. Also, the DELETE command works differently in the FSVIEW procedure than it does in the FSEDIT procedure. Refer to SAS/FSP Software: Usage and Reference for more information on this command.
The following example illustrates using the DELETE command in the FSEDIT procedure. Scroll forward to the record to be deleted and enter the DELETE command on the command line, as shown in Deleting a CA-DATACOM/DB Record.
Deleting a CA-DATACOM/DB Record
The DELETE command deletes the record and displays a message to that affect, as shown in The Deleted Row.
The Deleted Row
For more information on using the SAS/FSP procedures, see the SAS/FSP Software: Usage and Reference.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.