Browsing and Updating CA-Datacom/DB Data |
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 | |
INSERT | |
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 |
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.
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.
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
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 about the SAS SQL procedure, see the Base SAS Procedures Guide.
Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.