When using PROC SQL or a DATA Step with the PCFILES engine, you can receive the following error shown in this example below:
libname myxls pcfiles path='c:\sastest\myclass.xlsb';
NOTE: Libref MYXLS was successfully assigned as follows:
Engine: PCFILES
Physical Name: c:\sastest\myclass.xlsb
data mytest;
set myxls.'mysheet$'n;
ERROR: CLI prepare error: [Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
SQL statement: SELECT * FROM `mysheet$`.
run;
Where MYSHEET is the name of the EXCEL workbook referenced by the PCFILES LIBNAME engine.
The problem occurs when SAS® is used with UTF-8 encoding. To circumvent the problem, add UNICODE=YES to your LIBNAME statement as shown below:
libname myxls pcfiles path='c:\sastest\myclass.xlsb' unicode=yes;