Tag: ODBC

SAS Installation: ” Connect: Class not registered “

Issue: Installed SAS 9.4 on the machine where SAS 9.3 has already been installed. Not able to import Excel file in SAS 9.4.
Error message:
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement
Configuration:
– office 365 (32 bit)
– office 2010 (64 bit)
– Windows 7 (64 bit)
– SAS 9.3
– SAS 9.4 (64 bit)
– odbc 64bit: C:\Windows\System32\odbcad32.exe
– odbc 32bit: C:\Windows\SysWOW64\odbcad32.exe

Possible Reason: SAS PC Files Server not installed or not working properly. Need Excel ODBC driver in the same bitness as in SAS.
reference: http://support.sas.com/kb/60/356.html
Attempt 1: Stop SAS PC Files Server in Service (administrative tools); uninstal SAS PC Files Server; download SAS PC Files Server from (pcfilesrv__94200__wx6__en__web__1.zip) from https://support.sas.com/downloads/package.htm?pid=2167; unzip, install from setup.exe; get Java private environment error when installing PC Files Server.
Results 1: not working

Possible Reason: SAS 9.4 is not connected to the right ODBC. ODBC 32bit has Excel in the driver list but ODBC 64bit doesn’t have Excel in the driver list.
reference: https://support.microsoft.com/en-ca/help/942976/odbc-administrator-tool-displays-both-the-32-bit-and-the-64-bit-user-d
Attempt 2: run as administrator on both ODBC files; add excel driver as the system data source in the “System DSN” tab; change target from “\system32\odbcad32.exe” to \syswow64\odbcad32.exe” in the property of “Data Sources (ODBC) in Administrative Tools
Results 2: not working

Possible Reason: office 365 doesn’t have the match Access Engine for program external to Microsoft Office to access the Office 365 application data
Attempt 3:
— download Access Database engine 2016 distribution (32bit)
— install to cmd, cd to where the installation file is saved, run AccessDatabaseEngine.exe /quiet /passive
— open registry editor on window 7: windows key + r -> run regedit -> yes
— navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\FilesPaths, delete mso.dll
reference: http://support.sas.com/kb/43/933.html
https://knowledge.autodesk.com/support/autocad-map-3d/learn-explore/caas/screencast/Main/Details/7132d430-dc09-40bc-9148-94ff9db41c24.html
Results 3: not sure why but WORKED, but doesn’t work on the Office 365 32bit installation.

Attempt 4:
— uninstall Microsoft Office 365 (32bits)
— install Microsoft Office 365 (64bits)
— uninstall Access Database engine 2016 distribution (32bits)
— install Access Database engine 2016 distribution (64bits)
— install to cmd, cd to where the installation file is saved, run AccessDatabaseEngine.exe /quiet /passive
— open registry editor on window 7: windows key + r -> run regedit -> yes
— navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\FilesPaths, delete mso.dll
Results 4: worked. matched SAS 64bits with Office 365 64bits

SAS ODBC Driver Installation

I want to access the sas data table outside sas software and in Microsoft Access where I can edit the records more conveniently.

The version of sas I have is 9.3 and I googled the sas ODBC driver and came across the following link.

http://support.sas.com/downloads/browse.htm?cat=40#tab:2:

SAS3-1

Initially, I installed the “Aug 2014” version, but it is for SAS 9.4 and didn’t work.

I uninstalled the sas ODBC for 9.4 and then installed the “July 2011” version which didn’t work either because I have Win 7.

Then I had to uninstalled ODBC for 9.3 and download the “Dec 2011” version.

Hope this time it works but it doesn’t. 🙁

Got this error message.

SAS3-2

When launching setup.exe for ODBC installation. Got the following error message.

SAS3-3

While having no success in installing SAS ODBC 9.3,  I found some good reference resource on the website.

http://www.sascommunity.org/planet/blog/category/sql/

With some investigation, I found the odbcad32.exe hiding in a winsxs folder.

C:\Windows\winsxs\x86_microsoft-windows-m..-odbc-administrator_31bf3856ad364e35_6.1.7600.16385_none_44263d819f0aa19e

This might came from one of the windows service patch installed previously, but it is not the same ODBC administrator when I run from the “Control Panel” > “Administrative Tools”, which point to a different odbcad32.exe under C:\Windows\System32.

Apparently, the SAS ODBC 9.4 has already been recognized in the “ODBC Data Source Administrator”.  Under the “System DSN” tab, click “Add”.

SAS3-5

Find the “SAS” driver at the bottom of the list and click “Finish”.

SAS3-6

In the “SAS ODBC Driver Configuration” window, there are 3 tabs, “General”, “Servers” and “Libraries”. Notice the Server name is defined as “__5001” which is the default “Local (Single User)” and if you change the name to something else, it will cause error in connection. Click “Configure…” on the “Servers” tab.

SAS3-7

On the “Local Options” setting, check if the Path for sas.exe is correct and don’t change the default setting for “Startup parameters”.  Click “OK” to go back.

SAS3-8

Click “Library” tab and under the “Library Setting” give a Name for the library which holds the SAS data file you want to access through ODBC. In the “Host File”, provide the directory of the folder that contains the SAS data files.  You can create many libraries for different SAS folders. Click “Add” to add the library and then “OK” to go back.

SAS3-9

This completes the configuration of the “SAS ODBC Driver”.

 

 

I. A.