Chapter Contents

Previous

Next

UPDATE

	Optional statement
Applies to:	access descriptor or view descriptor

Details

The UPDATE statement identifies an existing access descriptor or view descriptor that you want to update. UPDATE is normally used to update database connection information, such as user names and passwords; if your descriptor requires many changes, it might be easier to use the CREATE statement to overwrite the old descriptor with a new one.

The descriptor that you update can exist in either a temporary (WORK) or permanent SAS data library. If the descriptor has been protected with a SAS password that prohibits editing of the ACCESS or VIEW descriptor, then the password must be specified on the UPDATE statement.

To update a descriptor, use its three-level name. The first level identifies the libref of the SAS data library where you stored the descriptor. The second level is the descriptor's name (member name). The third level is the type of SAS file.

You can use the UPDATE statement as many times as necessary in one procedure execution. That is, you can update multiple access descriptors, as well as one or more view descriptors based on these access descriptors, within the same execution of the ACCESS procedure. Or, you can update access descriptors and view descriptors in separate executions of the procedure.

Rules that applied to the CREATE statement under Version 6 of SAS/ACCESS software apply to the UPDATE statement. For example, the SUBSET statement is valid only for updating view descriptors and it is not valid for access descriptors.

Note:   The following statements are not supported when using the UPDATE statement: ASSIGN, RESET, SELECT, and UNIQUE.  

Updating access and view descriptors

You can update view descriptors and access descriptors in the same execution of the ACCESS procedure or in separate executions.

When you update an access or view descriptor, you must place statements or groups of statements in a certain order after the PROC ACCESS statement and its options, as listed below:

1. UPDATE is usually the first statement after the PROC ACCESS statement or in a code block within a PROC ACCESS statement. Usually, you update a descriptor that already exists; however, you can create and update a descriptor in the same PROC ACCESS step.

2. Next, specify any information that you want to update, including any database connection statements, the TABLE statement, or any editing statements. If you are updating an access descriptor, you can use the DROP, FORMAT, LIST, or RENAME editing statements. If you are updating a view descriptor, you can use the DROP, FORMAT, LIST, RENAME, and SUBSET editing statements. FORMAT and RENAME can be specified only when ASSIGN=NO is specified in the access descriptor referenced by the view descriptor you are updating. The order of the statements within this group usually does not matter; see the individual statement descriptions for any restrictions.

   The following editing statements are not allowed when you specify the UPDATE statement: SELECT, RESET, ASSIGN, and UNIQUE. LIST can only be used with views.

3. Specify the RUN statement to execute the ACCESS procedure. If you specify QUIT instead of RUN, PROC ACCESS terminates without updating your descriptor. Alternately, you can specify another CREATE or UPDATE statement to execute the previous CREATE or UPDATE statement; your changes are saved when a new CREATE, UPDATE, or RUN statement is entered.

Note:   Altering a DBMS table that has descriptor files defined on it might invalidate these files or cause them to be outdated. If you recreate a table, add a new column to a table, or delete an existing column from a table, use the UDPATE statement to modify your descriptors to use the new information.  

Examples

The following example updates an access descriptor ADLIB.EMPLOY on the Oracle Rdb table EMPLOYEES and then re-creates a view descriptor VLIB.EMP1204, which was based on ADLIB.EMPLOY. The original access descriptor included all of the columns in the table. Using the LIST statement enables you to write all of the variables to the SAS log so you can see the complete access descriptor before you update it.

In this example, the SALARY and BIRTHDATE columns are dropped from the access descriptor so that users cannot see this data. Because SELECT and RESET are not supported when UPDATE is used, the view descriptor VLIB.EMP1204 must be re-created to omit the SALARY and BIRTHDATE columns.

proc access dbms=rdb;

   /* update access descriptor  */

   update adlib.employ.access;     
   drop salary birthdate;
   list all;

   /* re-create view descriptor  */

   create vlib.emp1204.view;     
   select empid hiredate dept jobcode gender 
      lastname firstname middlename phone;
   format empid 6.
          jobcode 5.
          hiredate datetime9.;
   subset where jobcode=1204;
run;

The following example updates a view descriptor VLIB.BDAYS from the ADLIB.EMPLOY access descriptor, which was created previously. In this example, the WHERE clause replaces the WHERE clause that was specified in the original view descriptor. The SUBSET  statement contains an ORACLE-specific SQL statement.

proc access dbms=oracle;
   update vlib.bdays.view;
   subset where hiredate= 
      to_date('10OCT1988','ddmonyyyy');
run;

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.