Usage Note 19070: Deleting data from a Microsoft Excel spreadsheet using SAS/ACCESS® software
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
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft Windows NT Workstation | 9.1 TS1M3 | |
| Microsoft Windows 2000 Server | 9.1 TS1M3 | |
| Microsoft Windows XP Professional | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Usage Note |
| Priority: | low |
| Date Modified: | 2008-05-02 14:45:47 |
| Date Created: | 2006-11-28 15:23:44 |