Usage Note 42151: PCFILES engine does not support MODIFY in a DATA Step
 |  |  |  |
The EXCEL engine supports modifying of Excel values in a DATA Step as shown below:
libname test excel 'c:\sastest\myfilenew.xlsx' SCANTEXT=NO;
data test."CLASS$"n;
modify test."CLASS$"n;
IF name ="ALICE" THEN age=30;
run;
libname test clear;
However the PCFILES engine does not support this and you can get the following error:
libname test pcfiles path='c:\sastest\myfilenew.xlsx' SCANTEXT=NO;
NOTE: Libref TEST was successfully assigned as follows:
Engine: PCFILES
Physical Name: c:\sastest\myfilenew.xlsx
data test."CLASS$"n;
modify test."CLASS$"n;
IF name ="ALICE" THEN age=30;
run;
ERROR: Error updating table entry: [Microsoft][ODBC Excel Driver]Optional
feature not implemented
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set TEST.'CLASS$'n.
NOTE: The data set TEST.'CLASS$'n has been updated. There were 0
observations rewritten, 0 observations added and 0 observations deleted.
NOTE: There were 1 rejected updates, 0 rejected adds, and 0 rejected
deletes.
The message occurs because the PCFILES engine calls the SAS PC File Server, which calls the Microsoft Excel ODBC driver. The EXCEL engine calls the Microsoft Access Connectivity Engine directly and does not use the Microsoft ODBC driver.
To circumvent the problem use the IMPORT procedure with the EXCELCS engine to import the file to a SAS data set and make the modifications. Then use the EXPORT procedure to export the SAS data set to the Excel workbook.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M3 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M3 | |||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M3 | |||
| Microsoft Windows XP 64-bit Edition | 9.2 TS2M3 | |||
| Microsoft Windows Server 2003 for x64 | 9.2 TS2M3 | |||
| Microsoft Windows Server 2008 for x64 | 9.2 TS2M3 | |||
| Windows 7 Enterprise x64 | 9.2 TS2M3 | |||
| Windows 7 Home Premium x64 | 9.2 TS2M3 | |||
| Windows 7 Professional x64 | 9.2 TS2M3 | |||
| Windows 7 Ultimate x64 | 9.2 TS2M3 | |||
| Windows Vista for x64 | 9.2 TS2M3 | |||
| 64-bit Enabled AIX | 9.2 TS2M3 | |||
| 64-bit Enabled HP-UX | 9.2 TS2M3 | |||
| 64-bit Enabled Solaris | 9.2 TS2M3 | |||
| HP-UX IPF | 9.2 TS2M3 | |||
| Linux | 9.2 TS2M3 | |||
| Linux for x64 | 9.2 TS2M3 | |||
| Solaris for x64 | 9.2 TS2M3 | |||
The PCFILES engine does not support modifying Excel values in a DATA Step. To circumvent the problem, import the file into SAS first
then modify the SAS data set.
| Type: | Usage Note |
| Priority: |
| Date Modified: | 2011-01-24 16:56:21 |
| Date Created: | 2011-01-21 13:50:47 |