SAS File Import with Office 2019 or O365 for Windows 10
Users who need to import files in SAS that have Office 2019 installed will find that the SAS File Import Wizard fails with error “Connection Failed”, or they will receive “error in the LIBNAME statement” when importing via code.
Office 2019 is installed with click-to-run technology, which means some third party applications cannot see Microsoft library files where they have been historically kept as with the previous versions of MSI-installed Office.
Workaround:
Users can change their SAS coding and manually import files through code instead of using the Import Wizard. XLS and XLSX files apparently need to be converted to a CSV file with Excel, and then these SAS commands run:
PROC EXPORT to EXCEL
PROC EXPORT data=candida.master3 OUTFILE= “C:\Users\jls113\Documents\UNC MPH\Thesis\3_4.xlsx” dbms=xlsx;
RUN;
PROC IMPORT from EXCEL (make sure it is a csv file)
PROC IMPORT DATAFILE= “C:\Users\jls113\Documents\UNC MPH\Thesis\2_28.csv” OUT=work.data;
RUN;
Actual Fix:
To resolve this issue, install the “Microsoft Access 2013 Runtime” from this link:
https://www.microsoft.com/en-us/download/details.aspx?id=39358
Why 2013 Access Runtime?
SAS Cannot find the older MSI Access Engine in it’s typical place to run file imports, because of Office moving to click-to-run installations. Therefore we have to manually install the older MSI-based Access engine for SAS to import files.
Complicating the matter is that Microsoft actually provides Access Runtime versions for O365 (which should work with Office 2019, as Office 2019 is simply a feature-frozen early version of O365), and also an Access Runtime for 2016 – however, attempting to install either the Access Runtime for O365 or for 2016 while Office 2019 is installed will result in the Access Runtime installer asking you to remove Office 2019.
For some unknown reason found online – only the 2013 version of Access Runtime will install with Office 2019. (Even though the O365 Access Runtime version should be correct for 2019)
Error for searching:
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
NOTE: Export Cancelled.
1 libname candida "C:\Users\jls113\Documents\UNC MPH\Thesis";
NOTE: Libref CANDIDA was successfully assigned as follows:
Engine: V9
Physical Name: C:\Users\jls113\Documents\UNC MPH\Thesis
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
NOTE: Attachments for -9 reestablished for new parent.
NOTE: Export Cancelled.
This solution installing 2013 Access Run Time solved my problem with importing Excel 365 ProPlus to SAS. I have the lasted Office edition. So I assume it is the 2019. To my surprise, my computer had the 2010 Access Run Time and it was 32 bit. I had to uninstall the 32 bit Access Run Time first before being able to install the 2013 Access Run Time 64 bit.
Thank you. This post is very helpful and seems to be the only one on the web to address this particular issue. Even SAS blog does not have a solution yet.