Data cannot be deleted from a Microsoft Excel spreadsheet using SAS/ACCESS^® Interface to OLE DB, SAS/ACCESS^® Interface to PC Files, and SAS/ACCESS^® Interface to ODBC. Although you can insert or update records in the Excel workbook, you cannot perform any DELETE operations. SAS/ACCESS software cannot submit any code to Excel that will delete a physical worksheet or that will delete data from a worksheet.

The following example illustrates a way to work around this limitation.

Example

If you use the OLEDB engine in a LIBNAME statement, you can use the Jet OLE DB Provider to assign a libref to an Excel workbook. You can also specify the Excel worksheets and the Excel named ranges in SAS code, as follows:

  libname mylib oledb
          provider="Microsoft.Jet.OLEDB.4.0"
          preserve_tab_names=yes
          preserve_col_names=yes
          properties=('data source'="c:\class.xls")
          provider_string="Excel 8.0";

   proc print data=mylib.class; 
   run;

If you try to delete one or more records, you might receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

This limitation is inherent in the treatment of Excel workbooks as databases.

You can, however, drop a named range, as follows:

   proc sql;
      drop table mylib.NamedRange;
   quit;

You can also delete a row, in essence, by making all of the worksheet cells blank, as shown in this example:

   update mylib.class set name='', 
                          sex='' , 
                          age=.,        
                          height=.,
                          weight=. 
          where sex eq 'F';

Note, however, that this method leaves gaps between the rows.

Operating System and Release Information