SAS/ACCESS Interface to ADABAS Software

Browsing and Updating with the SQL Procedure

The SAS System SQL procedure also enables you to retrieve and update ADABAS data. You can retrieve and browse ADABAS 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 DELETE, INSERT, and UPDATE statements. You must have access to the data through appropriate ADABAS and/or NATURAL security options before you can edit ADABAS data. Here is a summary of these PROC SQL statements:

DELETE deletes logical records from an ADABAS file.

INSERT inserts logical records in an ADABAS file.

SELECT retrieves and displays data from an ADABAS file. A SELECT statement is usually referred to as a query because it queries the ADABAS file for information.

UPDATE updates values in an ADABAS file.

When using the SQL procedure, 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 SELECT statement to browse ADABAS data described by a view descriptor. The query in the following example retrieves and displays specified data fields and logical records in the CUSTOMERS DDM that are described by the VLIB.USACUST view descriptor. The LINESIZE= system option is used to reset the default output width to 120 columns.

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

options linesize=120;

proc sql;
   title 'ADABAS Data Output by a 
      SELECT Statement';
select custnum, state, name, limit,signatur
   from vlib.usacust;

ADABAS Data Output by a PROC SQL Query displays the query's results. Notice in the output that the SQL procedure displays the ADABAS data field names, not the corresponding SAS variable names.

ADABAS Data Output by a PROC SQL Query

ADABAS Data Output by a SELECT Statement CUSTOMER STATE NAME LIMIT SIGNATURE ------------------------------------------------------------------------------------------------- 12345678 NC 0.00 14324742 CA SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 5000.00 BOB HENSON 14324742 CA SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 25000.00 KAREN DRESSER 14569877 NC PRECISION PRODUCTS 5000.00 JEAN CRANDALL 14569877 NC PRECISION PRODUCTS 100000.00 STEVE BLUNTSEN 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 10000.00 MASON FOXWORTH 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 50000.00 DANIEL STEVENS 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 100000.00 ELIZABETH PATTON 15432147 MI GREAT LAKES LABORATORY EQUIPMENT MANUFACTURERS 10000.00 JACK TREVANE 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 10000.00 NANCY WALSH 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 50000.00 TED WHISTLER 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 100000.00 EVAN MASSEY 19783482 VA TWENTY-FIRST CENTURY MATERIALS 5000.00 PETER THOMAS 19783482 VA TWENTY-FIRST CENTURY MATERIALS 10000.00 LOUIS PICKERING 19876078 CA SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 7500.00 EDWARD LOWE 19876078 CA SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 25000.00 E.F. JENSEN

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

title 'ADABAS Data Output by a WHERE Clause';
select custnum, state, name, limit, signatur
   from vlib.usacust
   where state='NC';

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

ADABAS Data Output Subset by a WHERE Clause

ADABAS Data Output by a WHERE Clause CUSTOMER STATE NAME LIMIT SIGNATURE ------------------------------------------------------------------------------------------------- 12345678 NC 0.00 14569877 NC PRECISION PRODUCTS 5000.00 JEAN CRANDALL 14569877 NC PRECISION PRODUCTS 100000.00 STEVE BLUNTSEN

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

The following UPDATE statements update the values described by the logical record that meets the WHERE clause criteria. The SELECT statement then displays the view's output as shown in ADABAS Data Updated by the UPDATE Statement. The ORDER BY clause in the SELECT statement causes the data to be presented in ascending order by the CUSTOMER data field. (Because you are referencing a view descriptor, you use the SAS variable names for data fields in the UPDATE statement; however, the SQL procedure displays the ADABAS data field names.)

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';
update vlib.usacust
   set limit=5000.00
   where custnum='12345678';
update vlib.usacust
   set signatur='MARC PLOUGHMAN'
   where custnum='12345678';
update vlib.usacust
   set branch_2='DURHAM'
   where custnum='12345678';
title 'Updated ADABAS Data in CUSTOMERS';
select custnum, state, name, limit, signatur
   from vlib.usacust;

ADABAS Data Updated by the UPDATE Statement

Updated ADABAS Data in CUSTOMERS CUSTOMER STATE NAME LIMIT SIGNATURE ------------------------------------------------------------------------------------------------- 12345678 NC DURHAM SCIENTIFIC SUPPLY COMPANY 5000.00 MARC PLOUGHMAN 14324742 CA SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 5000.00 BOB HENSON 14324742 CA SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 25000.00 KAREN DRESSER 14569877 0 NC PRECISION PRODUCTS 5000.00 JEAN CRANDALL 14569877 NC PRECISION PRODUCTS 100000.00 STEVE BLUNTSEN 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 10000.00 MASON FOXWORTH 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 50000.00 DANIEL STEVENS 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 100000.00 ELIZABETH PATTON 15432147 MI GREAT LAKES LABORATORY EQUIPMENT MANUFACTURERS 10000.00 JACK TREVANE 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 10000.00 NANCY WALSH 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 50000.00 TED WHISTLER 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 100000.00 EVAN MASSEY 19783482 VA TWENTY-FIRST CENTURY MATERIALS 5000.00 PETER THOMAS 19783482 VA TWENTY-FIRST CENTURY MATERIALS 10000.00 LOUIS PICKERING 19876078 CA SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 7500.00 EDWARD LOWE 19876078 CA SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 25000.00 E.F. JENSEN

You can use the INSERT statement to add logical records to an ADABAS file or the DELETE statement to remove logical records. In the following example, the logical record containing the CUSTOMER value 15432147 is deleted by using the CUSTOMERS DDM. The SELECT statement then displays the VLIB.USACUST data in ADABAS Data with a Logical Record Deleted, ordering them again by the CUSTOMER data field.

delete from vlib.usacust
   where custnum='15432147';
   title 'Logical Record Deleted from 
      CUSTOMERS';
select custnum, state, name, limit, signatur
   from vlib.usacust;

ADABAS Data with a Logical Record Deleted

Updated ADABAS Data in CUSTOMERS CUSTOMER STATE NAME LIMIT SIGNATURE ------------------------------------------------------------------------------------------------- 12345678 NC DURHAM SCIENTIFIC SUPPLY COMPANY 5000.00 MARC PLOUGHMAN 14324742 CA SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 5000.00 BOB HENSON 14324742 CA SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS 25000.00 KAREN DRESSER 14569877 NC PRECISION PRODUCTS 5000.00 JEAN CRANDALL 14569877 NC PRECISION PRODUCTS 100000.00 STEVE BLUNTSEN 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 10000.00 MASON FOXWORTH 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 50000.00 DANIEL STEVENS 14898029 MD UNIVERSITY BIOMEDICAL MATERIALS 100000.00 ELIZABETH PATTON 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 10000.00 NANCY WALSH 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 50000.00 TED WHISTLER 18543489 TX LONE STAR STATE RESEARCH SUPPLIERS 100000.00 EVAN MASSEY 19783482 VA TWENTY-FIRST CENTURY MATERIALS 5000.00 PETER THOMAS 19783482 VA TWENTY-FIRST CENTURY MATERIALS 10000.00 LOUIS PICKERING 19876078 CA SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 7500.00 EDWARD LOWE 19876078 CA SAN JOAQUIN SCIENTIFIC AND INDUSTRIAL SUPPLY, INC. 25000.00 E.F. JENSEN

CAUTION:: Always use the WHERE clause in a DELETE statement. If you omit the WHERE clause from a DELETE statement, you delete all the data in the ADABAS file that is accessed by the view descriptor.

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

Chapter Contents
Previous
Next
Top of Page

DELETE	deletes logical records from an ADABAS file.
INSERT	inserts logical records in an ADABAS file.
SELECT	retrieves and displays data from an ADABAS file. A SELECT statement is usually referred to as a query because it queries the ADABAS file for information.
UPDATE	updates values in an ADABAS file.