Chapter Contents |
Previous |
Next |
SAS/ACCESS Interface to CA-DATACOM/DB: Reference |
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.
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.
The SELECT Statement |
Note: The following SQL procedure examples
assume 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;
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 |
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 |
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 |
For more information on the SAS System SQL procedure, see the SAS Procedures Guide.
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.