ERROR: Error trying to establish connection: Unable to create Data Source.: Class notThe original SAS code for reading Excel and Access files are as follows:
ERROR: Error in the LIBNAME statement.
libname xlsfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data source=c:\temp\ExcelFile.xlsx; extended Properties=Excel 12.0";Working with SAS technical support, I found that our tech guy installed Office 32-bit on my machine. SAS 64-bit could not use the 32-bit Office drivers. But what I understand from technical support was even if I had Office 64-bit installed, I still would encounter some issues because 64-bit is officially supported in SAS version 9.3. They mentioned something called PC Server that could help me. I read up about it online, and eventually found a solution.
libname accfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\temp\AccessFile.accdb; Persist Security Info=True";
First, I have to install SAS PC Server component on my machine. Then I need to change the SAS code to the followings accordingly:
libname xlsfile pcfiles type=Excel port=8621 server=localhost path = "c:\temp\ExcelFile.xlsx";Thanks to this article DID YOU KNOW THAT YOU CAN NOW ACCESS PC FILES ON A WINDOWS MACHINE DIRECTLY FROM A UNIX MACHINE? which helped me solve the problem.
libname accfile pcfiles type=Access port=8621 server=localhost path = "c:\temp\AccessFile.accdb";