SAS/ACCESS Interface to CA-DATACOM/DB: Reference

Browsing and Updating with the SQL Procedure

The SAS System SQL procedure also enables you to retrieve and update CA-DATACOM/DB data. You can retrieve and browse the data by specifying a view descriptor in a PROC SQL SELECT statement.

To update the data, you can specify view descriptors in the PROC SQL INSERT, DELETE, and UPDATE statements. Here is a summary of these PROC SQL statements:

DELETE deletes records from a CA-DATACOM/DB table.

INSERT inserts records into a CA-DATACOM/DB table.

SELECT retrieves and displays data from CA-DATACOM/DB tables. A SELECT statement is usually referred to as a query, because it queries the tables for information.

UPDATE updates records in a CA-DATACOM/DB table.

When using the SQL procedure in interactive line mode, note that the data are displayed in the SAS OUTPUT window. The procedure displays output data automatically without using the PRINT procedure and executes without using the RUN statement when an SQL procedure statement is executed. You can use the QUIT statement if you want to exit the SQL procedure.

CAUTION:

When you use the SQL procedure for update processing (DELETE, INSERT, and UPDATE statements), you must set the SQL procedure option, UNDO_POLICY. The SQL procedure supports backouts of group updates for those databases that support member-level locking. CA-DATACOM/DB software does not support member-level locks. The UNDO_POLICY option allows updates to be processed without backouts. For the CA-DATACOM/DB interface, you set the value of the option to NONE. For example:

proc sql undo_policy=none;
  update vlib.usacust
  set zipcode=27702
  where custnum='12345678';

If the update is processed successfully, it is applied to the database table and a warning message is issued. The message signifies that if multiple records were updated by the command and a failure occurred some time after the first record was successfully processed, then there is no way for PROC SQL to avoid a partial update.

Partial updating means that some records are updated and some are not. It does not mean that some fields in the same record are updated while other fields are not. [cautionend]

The SELECT Statement

You can use the SELECT statement to browse CA-DATACOM/DB data described by a view descriptor. The query in the following example retrieves and displays all the fields and records in the CUSTOMERS table that are described by the VLIB.USACUST view descriptor. The UNDO_POLICY procedure option is included to disable member-level locking and enable updates later in the PROC SQL execution. You can exclude the UNDO_POLICY option if you do not plan to perform updates. The LINESIZE= system option is used to reset the default output width to 120 columns.

Note: The following SQL procedure examples assume the CUSTOMERS table has not been updated by the earlier SAS/FSP examples. [cautionend]

options linesize=120

proc sql undo_policy=none;
   title 'CA--DATACOM/DB Data Output from a SELECT Statement';
select custnum, state label='STATE', zipcode label='ZIPCODE',
       name, firstord
   from vlib.usacust;

CA-DATACOM/DB. Data Output from a PROC SQL Query displays the query's results. Notice that the SQL procedure displays the CA-DATACOM/DB field names, not the corresponding SAS column names.

CA-DATACOM/DB. Data Output from a PROC SQL Query

CA-DATACOM/DB Data Output from a SELECT Statement CUSTOMER STATE ZIPCODE NAME FIRSTORDERDATE ------------------------------------------------------------------------------------------- 12345678 NC . . 14324742 CA 95123 SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 05FEB65 19783482 VA 22090 TWENTY-FIRST CENTURY MATERIALS 18JUL68 14898029 MD 20850 UNIVERSITY BIOMEDICAL MATERIALS 12NOV76 19876078 CA 93274 SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 11MAY79 18543489 TX 78701 LONE STAR STATE RESEARCH SUPPLIERS 10SEP79 14569877 NC 27514 PRECISION PRODUCTS 15AUG83 15432147 MI 49001 GREAT LAKES LABORATORY EQUIPMENT MANUFACTURERS 28APR86

You can specify a WHERE clause as part of the SELECT statement to subset the records for display. This example displays the companies that are located in North Carolina.

title 'CA--DATACOM/DB Data Output Subset by a WHERE Clause';
select custnum, state label='STATE', zipcode label='ZIPCODE',
       name, firstord
   from vlib.usacust
   where state='NC';

Notice that the PROC SQL statement is not repeated in this query. You do not need to repeat the PROC statement unless you use another SAS procedure, the DATA step, or a QUIT statement between PROC SQL statements. CA-DATACOM/DB. Data Output Subset by a WHERE Clause displays the two companies from North Carolina described by VLIB.USACUST.

CA-DATACOM/DB. Data Output Subset by a WHERE Clause

CA-DATACOM/DB Data Output Subset by a WHERE Clause CUSTOMER STATE ZIPCODE NAME FIRSTORDERDATE --------------------------------------------------------------- 12345678 NC . . 14569877 NC 27514 PRECISION PRODUCTS 15AUG83

The UPDATE Statement

You can use the UPDATE statement to update CA-DATACOM/DB data. Remember that when you reference a view descriptor in a PROC SQL statement, you are not updating the view descriptor, but rather the CA-DATACOM/DB data described by the view descriptor.

The following UPDATE statements update the values described by the first record of VLIB.USACUST. The SELECT statement then displays the view's output. The ORDER BY clause in the SELECT statement causes the data to be presented in ascending order by the CUSTNUM field. The UNDO_POLICY option is omitted since it was specified in the original SQL request.

update vlib.usacust
   set zipcode=27702
   where custnum='12345678';
update vlib.usacust
   set name='DURHAM SCIENTIFIC SUPPLY COMPANY'
   where custnum='12345678';
update vlib.usacust
   set firstord='02jan88'd
   where custnum='12345678';
   title 'Updated VLIB.USACUST View Descriptor';
select custnum, state label='STATE', zipcode label='ZIPCODE', name, 
   firstord from vlib.usacust
   order by custnum;

Updated VLIB.USACUST View Descriptor displays the query's results.

Updated VLIB.USACUST View Descriptor

Updated VLIB.USACUST View Descriptor CUSTOMER STATE ZIPCODE NAME FIRSTORDERDATE --------------------------------------------------------------------------------------------- 12345678 NC 27702 DURHAM SCIENTIFIC SUPPLY COMPANY 02JAN88 14324742 CA 95123 SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 05FEB65 14569877 NC 27514 PRECISION PRODUCTS 15AUG83 14898029 MD 20850 UNIVERSITY BIOMEDICAL MATERIALS 12NOV76 15432147 MI 49001 GREAT LAKES LABORATORY EQUIPMENT MANUFACTURERS 28APR86 18543489 TX 78701 LONE STAR STATE RESEARCH SUPPLIERS 10SEP79 19783482 VA 22090 TWENTY-FIRST CENTURY MATERIALS 18JUL68 19876078 CA 93274 SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 11MAY79

The INSERT and DELETE Statements

You can use the INSERT statement to add records to a CA-DATACOM/DB table or the DELETE statement to remove records. In the following example, the record containing the CUSTNUM value 15432147 is deleted from the table CUSTOMERS. The SELECT statement then displays the VLIB.USACUST data, ordering them again by the CUSTNUM field. Again, the UNDO_POLICY option was omitted because it was specified in the original SQL request and no intervening SAS procedure, DATA step, or QUIT statement occurred between SQL statements.

delete from vlib.usacust
   where custnum='15432147';
title 'Record Deleted from CA-DATACOM/DB CUSTOMERS Table';
select custnum, state label='STATE', zipcode label='ZIPCODE',
       name, firstord
   from vlib.usacust
   order by custnum;

VLIB.USACUST Data with a Record Deleted displays the query's results.

VLIB.USACUST Data with a Record Deleted

Record Deleted from CA-DATACOM/DB CUSTOMERS Table CUSTOMER STATE ZIPCODE NAME FIRSTORDERDATE ---------------------------------------------------------------------------------------------- 12345678 NC 27702 DURHAM SCIENTIFIC SUPPLY COMPANY 02JAN88 14324742 CA 95123 SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 05FEB65 14569877 NC 27514 PRECISION PRODUCTS 15AUG83 14898029 MD 20850 UNIVERSITY BIOMEDICAL MATERIALS 12NOV76 18543489 TX 78701 LONE STAR STATE RESEARCH SUPPLIERS 10SEP79 19783482 VA 22090 TWENTY-FIRST CENTURY MATERIALS 18JUL68 19876078 CA 93274 SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 11MAY79

CAUTION:: Always use the WHERE clause in a DELETE statement. If you omit the WHERE clause from the DELETE statement, you will delete all the data in the CA-DATACOM/DB table accessed by the view descriptor.

For more information on the SAS System SQL procedure, see the SAS Procedures Guide.

Chapter Contents
Previous
Next
Top of Page

DELETE	deletes records from a CA-DATACOM/DB table.
INSERT	inserts records into a CA-DATACOM/DB table.
SELECT	retrieves and displays data from CA-DATACOM/DB tables. A SELECT statement is usually referred to as a query, because it queries the tables for information.
UPDATE	updates records in a CA-DATACOM/DB table.