Browsing and Updating CA-Datacom/DB Data with the SQL Procedure :: SAS/ACCESS(R) 9.3 Interface to CA-Datacom/DB: Reference

Using the SQL Procedure

The SAS 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 is 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 enables 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.

Browsing Data with the SELECT Statement

You can use the SELECT statement to browse CA-Datacom/DB data that is 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 option is included to disable member-level locking and to 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 that the CUSTOMERS table has not been updated by the earlier SAS/FSP examples.

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;

The following output shows the query's results. Notice that the SQL procedure displays the CA-Datacom/DB field names, not the corresponding SAS column names.

Results of 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. The following output displays the two companies from North Carolina described by Vlib.UsaCust.

Results of PROC SQL Query 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

Updating Data with 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 that is 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;

The following output displays the query's results.

Results of Updating Data with an UPDATE Statement

                                            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

Adding and Removing Data with 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;

The following output displays the query's results.

Results of Removing Data with a DELETE Statement

                        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 delete all the data in the CA-Datacom/DB table accessed by the view descriptor.

For more information about the SAS SQL procedure, see the SAS SQL Procedure User's Guide.