SAS/ACCESS Software for PC File Formats: Reference

Reading and Updating Data with the SQL Procedure

The SAS System's SQL procedure enables you to retrieve data from PC files and update data in DBF files. You can read and display PC files data by specifying a view descriptor or other SAS data set in the SQL procedure's SELECT statement.

To update DBF data, you can specify view descriptors in the SQL procedure's INSERT, DELETE, and UPDATE statements. You can also use these statements to modify SAS data files. However, the ability to update data in a DBF file 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.
If you did not create the DBF file, you must be granted the appropriate file access privileges before you can select, insert, delete, or update the data.
You must also be granted the appropriate file access privileges before you select the data from DIF, WKn, or XLS files. The SAS/ACCESS interface to these files is read-only, so the SELECT statement is the only one of the four PROC SQL statements (in this section) that can reference a view descriptor based on DIF, WKn, or XLS data.

A summary of some of the SQL procedure statements follows:

SELECT retrieves, manipulates, and displays PC file data that is described by a view descriptor. SELECT can also use data described by a PROC SQL or DATA step view or data in a SAS data file. A SELECT statement is usually referred to as a query because it queries the table for information.

DELETE deletes rows from a SAS data file or from a DBF file that is described by a view descriptor. When you reference a view descriptor that is based on a DBF file in the DELETE statement, the records in the DBF file are marked for deletion.

INSERT inserts rows into a DBF file or a SAS data file.

UPDATE updates the data values in a DBF file or in a SAS data file.

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 PC files column names. If you prefer to use SAS variable names in your output, specify NOLABEL in the OPTIONS statement.

For more information about this procedure, its options, and example, see the SQL procedure chapter in SAS Procedures Guide.

Reading Data with the SQL Procedure

You can use the SQL procedure's SELECT statement to display PC files data that are described by a view descriptor or by another SAS data set. In the following example, the query uses the VLIB.PRODUCT view descriptor to retrieve a subset of the data in the SPECPROD.DIF file.

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.

proc access dmbs=dif;
   create adlib.product.access;    
   /* create access descriptor  */
   path="c:\sasdemo\specprod.dif";
   diflabel;
   assign=yes;
   rename productid prodid;
   format productid  4.
          weight     e16.9
          fibersize  e20.13
          width      e16.9;
   list all;

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

options nodate linesize=120;
  title 'DIF File Data Retrieved with a SELECT 
         Statement';

proc sql;
  select *
  from vlib.product
  where cost is not null
  order by fibernam;
quit;

PC Files Data Retrieved with a PROC SQL Query displays the query's output. Notice that the SQL procedure displays the DIF file's column names, not the SAS variable names.

PC Files Data Retrieved with a PROC SQL Query

DIF File 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 DBF file, as illustrated by the following example. Because you are referencing a view descriptor, you use the SAS variable names in the UPDATE statement; however, the SQL procedure displays the DBF column names.

proc sql;
 update vlib.empeeoc
   set salary=26678.24,
       gender='M',
       birthdat='28AUG1959'd
   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;

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

DBF File 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 28AUG1959 VARGAS

Deleting Data with the SQL Procedure

You can use the SQL procedure's DELETE statement to delete rows from a DBF file. In the following example, the row that contains the value 346917 in the EMPID column is deleted from the EMPLOYEE.DBF.

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

The deleted observation is marked with an asterisk (*) in the DELETE_FLG field. This is the only indicator you have that a record in a DBF field has been marked for deletion. If you have a number of rows to delete, you could use a macro variable EMPID instead of the individual EMPID values. Doing so would enable you 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 the DBF file.

Inserting Data with the SQL Procedure

You can use the SQL procedure's INSERT statement to add rows to a DBF file. In the following example, the row that contains the value 346917 in the EMPID column is inserted back into the EMPLOYEE.DBF file.

proc sql;
  insert into vlib.allemp
   values(",346917,'02MAR1987'd,46000.33,'SHP013',204,
          'F','15MAR1950'd,'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 6701 6702 6703 proc sql; 6704 insert into vlib.allemp 6705 values(",346917,'02MAR1987'd,46000.33, 6706 'SHP013',204,'F','15MAR1950'd, 6707 'SHIEKELESLAM','SHALA','Y.', 6708 '8745'); NOTE: 1 row was inserted into VLIB.ALLEMP. 6709 quit;

Chapter Contents
Previous
Next
Top of Page

SELECT	retrieves, manipulates, and displays PC file data that is described by a view descriptor. `SELECT` can also use data described by a PROC SQL or DATA step view or data in a SAS data file. A SELECT statement is usually referred to as a query because it queries the table for information.
DELETE	deletes rows from a SAS data file or from a DBF file that is described by a view descriptor. When you reference a view descriptor that is based on a DBF file in the DELETE statement, the records in the DBF file are marked for deletion.
INSERT	inserts rows into a DBF file or a SAS data file.
UPDATE	updates the data values in a DBF file or in a SAS data file.