SAS/ACCESS Software for Relational Databases: Reference

Reading and Updating Data with the SQL Procedure

In Version 6, the SAS System's SQL procedure enabled you to retrieve and update data from DBMS tables and views. You could read and display DBMS data by specifying a view descriptor or other SAS data set in the SQL procedure's SELECT statement. In Version 7, you can specify librefs based on DBMS data that you create by using the SAS/ACCESS LIBNAME statement. See Using DBMS Data in Version 7 and Version 8 for examples.

If you are using descriptors, you can specify them in the SQL procedure's INSERT, DELETE, and UPDATE statements. You can also use these statements to modify SAS data files. The ability to update data in a DBMS table or through a DBMS view by using descriptors is subject to the following conditions:

As in other PROC and DATA steps, you can use only a view descriptor or other SAS data set in an SQL procedure statement, not an access descriptor.
You can usually only browse data retrieved using a view descriptor that is based on a DBMS view. Most DBMS views have a number of restrictions on when you can use them to update their underlying data. (The primary restriction is that a DBMS view can be used only to update data when the view is defined on a single DBMS table.)
If you did not create the DBMS table or view, you must be granted the appropriate DBMS privileges before you can select, insert, delete, or update the data.
A DBMS trigger might prevent you from updating observations in a DBMS table. Refer to your DBA to determine if triggers are used in your DBMS tables.
Some DBMSs require the SQL procedure's UNDO_POLICY option before you can use the INSERT, DELETE, or UPDATE statements with a DBMS view descriptor. For example:
```
proc sql undo_policy=none;
```
Refer to your DBMS chapter to see if this requirement applies to your DBMS.

Here is a summary of some of the SQL procedure statements, when used with view descriptors:

SELECT retrieves, manipulates, and displays data described by a view descriptor. A SELECT statement is usually referred to as a query because it queries the table for information.

DELETE deletes rows from a DBMS table that is described by a view descriptor. If a view descriptor is based on an updatable DBMS view, rows can also be deleted from the view's underlying table.

INSERT inserts rows into a DBMS table.

UPDATE updates the data values in a DBMS table.

Because the SQL procedure is based on the Structured Query Language, it works somewhat differently than some SAS procedures. For example, the SQL procedure executes without a RUN statement when a procedure statement is submitted. The SQL procedure also displays any output automatically without using the PRINT procedure.

By default, PROC SQL uses the LABEL option to display output. LABEL displays SAS variable labels, which default to DBMS column names. If you prefer to use SAS variable names in your output, specify NOLABEL in the OPTIONS statement.

Reading Data with the SQL Procedure

You can use the SQL procedure's SELECT statement to display data that is described by a view descriptor. In the following example, the query uses the VLIB.PRODUCT view descriptor to retrieve a subset of the data in the ORACLE SPECPROD table.

The asterisk (*) in the SELECT statement indicates that all the columns in VLIB.PRODUCT are retrieved. The WHERE clause retrieves a subset of the rows. The ORDER BY clause causes the data to be presented in ascending order according to the table's FIBERNAME column. Both the WHERE clause and the ORDER BY clause are passed to the DBMS for processing.

Note: The following SQL procedure examples assume that the DBMS tables have not been updated by other examples. [cautionend]

proc access dbms=oracle;
/* create access descriptor  */
   create adlib.product.access;
   user=scott;  orapw=tiger;
   path='myorapath';
   table=specprod;
   assign=yes;
   rename productid=prodid 
          fibername=fibernam;
   format productid  4.
          weight     e16.9
          fibersize  e20.13
          width      e16.9 ;
   list all;

/* create view descriptor   */
   create vlib.product.view;
   select all;
   list view;
 run;

options nodate linesize=120;
title 'DBMS Data Retrieved with a SELECT 
       Statement';
proc sql;
select *
  from vlib.product
  where cost is not null
  order by fibernam;
quit;

DBMS Data Retrieved with a PROC SQL Query displays the query's output. Note that the SQL procedure displays the DBMS table's column names, not the SAS variable names.

DBMS Data Retrieved with a PROC SQL Query

DBMS Data Retrieved with a SELECT Statement PRODUCTID WEIGHT FIBERNAME FIBERSIZE COST PERUNIT WIDTH ------------------------------------------------------------------------------------------------------------------ 1279 1.278899910E-01 asbestos 6.3476000000000E-10 1289.64 m 2.227550050E+02 2567 1.258500220E-01 fiberglass 5.1880000000000E-11 560.33 m 1.205000000E+02 8934 1.429999950E-03 gold 2.3800000000000E-12 100580.33 cm 2.255999760E+01

Updating Data with the SQL Procedure

You can use the SQL procedure's UPDATE statement to update the data in a DBMS table.

The following UPDATE statements update the values in the Oracle Rdb table EMPLOYEES. Because you are referencing a view descriptor, you use the SAS variable names in the UPDATE statement; however, the SQL procedure displays the Oracle Rdb column names.

Note: The following examples use a previously created view descriptor, VLIB.EMPEEOC, which is based on data that is contained in the EMPLOYEES table. [cautionend]

proc sql;
update vlib.empeeoc
   set salary=26678.24,
       gender='M',
       birthdat='28AUG64'dt
   where empid='123456';

options linesize=120;
title 'Updated Data in EMPLOYEES Table';
select empid, hiredate, salary, dept, 
       jobcode, gender, birthdat, lastname
   from vlib.empeeoc
   where empid='123456';
quit;

Oracle Rdb Data Updated with the UPDATE Statement displays the updated row of data retrieved from the view descriptor VLIB.EMPEEOC.

Oracle Rdb Data Updated with the UPDATE Statement

Updated Data in EMPLOYEES Table EMPID HIREDATE SALARY DEPT JOBCODE GENDER BIRTHDATE LASTNAME -------------------------------------------------------------------------------------- 123456 04APR1989 $26,678.24 ACC043 1204 M 28AUG64 VARGAS

Deleting Data with the SQL Procedure

You can use the SQL procedure's DELETE statement to delete rows from a DBMS table. In the following example, the row that contains the value 346917 in the EMPID column is deleted from the Oracle Rdb table EMPLOYEES.

proc sql undo_policy=none;
delete from vlib.empeeoc
   where empid='346917';
quit;

A message is written to the SAS log to indicate that the row has been deleted, as shown in Message Displayed in the SAS Log When a Row Is Deleted.

Message Displayed in the SAS Log When a Row Is Deleted

6688 6689 /*=========================*/ 6690 /* Example for Output */ 6691 /* shows in a SAS log. */ 6692 /*=========================*/ 6693 proc sql undo_policy=none; 6694 delete from vlib.empeeoc 6695 where empid='346917'; NOTE: 1 row was deleted from VLIB.EMPEEOC. 6707 quit;

If you have many rows to delete, you could use a macro variable for EMPID instead of the individual EMPID values to change the values more easily.

%let empid='346917';

proc sql;
delete from vlib.empeeoc
   where empid=&empid;
quit;

CAUTION:: Use a WHERE clause in the DELETE statement. If you omit the WHERE clause from the DELETE statement, you delete all the data in the SAS data file or DBMS table.

Inserting Data with the SQL Procedure

You can use the SQL procedure's INSERT statement to add rows to a DBMS table. In the following example, the row that contains the value 346917 in the EMPID column is inserted back into the Oracle Rdb table EMPLOYEES.

Note: The following examples use a previously created view descriptor, VLIB.ALLEMP, which is based on data contained in the EMPLOYEES table. [cautionend]

proc sql undo_policy=none;
insert into vlib.allemp
   values(346917,'02MAR87'd,46000.33,'SHP013',
          204,'F','15MAR1955'DT,'SHIEKELESLAM',
          'SHALA','Y.','8745');
quit;

A message is written to the SAS log to indicate that the row has been inserted, as shown in Message Displayed in the SAS Log When a Row Is Inserted.

Message Displayed in the SAS Log When a Row Is Inserted

6698 6699 /*=========================*/ 6700 /* Example for Output */ 6701 /* shows in a SAS log. */ 6702 /*=========================*/ 6703 proc sql undo_policy=none; 6704 insert into vlib.allemp 6705 values(346917,'02MAR87'd,46000.33,'SHP013',204,'F', 6706 '15MAR1955'DT, 'SHIEKELESLAM','SHALA','Y.','8745'); NOTE: 1 row was inserted into VLIB.ALLEMP. 6707 quit;

Chapter Contents
Previous
Next
Top of Page

`SELECT`	retrieves, manipulates, and displays data described by a view descriptor. A SELECT statement is usually referred to as a query because it queries the table for information.
`DELETE`	deletes rows from a DBMS table that is described by a view descriptor. If a view descriptor is based on an updatable DBMS view, rows can also be deleted from the view's underlying table.
`INSERT`	inserts rows into a DBMS table.
`UPDATE`	updates the data values in a DBMS table.