topic Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms in SAS Data Management

Using SAS/ACCESS Interface to ODBC on UNIX Platforms

BruceBrad — Fri, 15 Jun 2018 21:41:18 GMT

I want to read emails from an Outlook folder into a SAS table. I'm following the ODBC approach in http://support.sas.com/resources/papers/proceedings10/086-2010.pdf but I'm getting an error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] The specified
DSN contains an architecture mismatch between the Driver and Application
ERROR: Error in the LIBNAME statement.

I'm guessing this is some 32/64bit incompatibility. I'm using Outlook and Access 32 bit, but SAS 64bit (9.4TS1M3). Can this be made to work?

Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms

ballardw — Fri, 15 Jun 2018 22:09:59 GMT

As a minimum I suggest showing your LIbname statement. Someone may have an idea for what may be missing or incorrect.

Also since the reference paper is from 2010 and references Outlook 2007 and Access 2007 it may be fun digging out the changes that Micro$oft has made to the office interfaces. It might be and Office ODBC driver that needs to be updated.

Did you get the Access bit to connect to Outlook okay without involving SAS in any way?

Also can you connect to Access with a simple table or two successfully?

Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms

BruceBrad — Fri, 15 Jun 2018 22:55:21 GMT

The libname is simply:

libname LISin odbc dsn=LISin;

LISin is configured in the ODBC configuration to point to an access database (2000 format). The Access database can read the Outlook emails file. I haven't managed to get SAS to read any Access database. After reading some more, I'm sure the problem is a 64/32 bit compatibility issue.

Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms

ballardw — Fri, 15 Jun 2018 23:20:16 GMT

By any chance do you have the SAS/Access Interface to PCFiles licensed? If so a Libname lisin PCFILES path="path and name of accessdb" might work.

If you do have the Interface to PCFiles but things aren't working you might try downloading the PCFILES server which is one of the ways around the 64/32 bit issue. https://support.sas.com/downloads/browse.htm?fil=&cat=63

I no longer have Microsoft access and the one time I did the ODBC connection was with SAS 9.2 and was 32 bit, so that might be the issue.

Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms

BruceBrad — Mon, 18 Jun 2018 18:35:09 GMT

Got it working (sort of) after installing the 32 bit version of PC files server. My code is now:

proc import out=temp replace
            datatable="emails"
            dbms=accesscs
            ;
            database="emails.accdb";
            memosize=32767;
            run;

Unfortunately, the engine can only read long text fields up to 32767 bytes. The email content I want read is larger than this - so I'll have to think of some other approach.

Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms

ballardw — Mon, 18 Jun 2018 19:46:44 GMT

@BruceBrad wrote:

Got it working (sort of) after installing the 32 bit version of PC files server. My code is now:
proc import out=temp replace
            datatable="emails"
            dbms=accesscs
            ;
            database="emails.accdb";
            memosize=32767;
            run;
Unfortunately, the engine can only read long text fields up to 32767 bytes. The email content I want read is larger than this - so I'll have to think of some other approach.

As I mentioned I don't have Access. But a generic problem comes from using ProC Import in that the guessing procedure does just that and truncates long values. You might be able to write a data step to read the table and read the long field into multiple variables. Might.

Re: Using SAS/ACCESS Interface to ODBC on UNIX Platforms

BruceBrad — Thu, 21 Jun 2018 16:56:16 GMT

A summary of my resolution to this. First, I'm not sure how 'unix' got into the subject line. No unix involved in this.

My goal was to read incoming emails (from the Outlook client) into SAS. I now have a procedure that writes each email to a text file (which can be read by sas). The strategy is:

1) Configure an Outlook Rule that runs a VBA script against every candidate incoming email. In recent versions of Outlook you need to change a registry value to permit this.

[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Outlook\Security]"EnableUnsafeClientMailRules"=dword:00000001

2) Use a VBA script as per the following code. (You need to enable a reference to Microsoft Scripting Runtime for the FileSystemObject to work). (There is an alternative Savefile method in VBA but this wraps long lines of text).

Sub SaveLIS(Item As Outlook.MailItem)
Dim Received As String
Dim Subject As String
Received = Format(Item.ReceivedTime, "yyyy-mm-dd-hh-MM-ss")
Subject = strLegalFileName(Item.Subject)
Dim objFS As Scripting.FileSystemObject
Dim objOutputFile As Scripting.TextStream
Set objFS = New Scripting.FileSystemObject
Set objOutputFile = objFS.CreateTextFile("C:\temp\Returns\" & Subject & " R; " & Received & ".txt", True)
objOutputFile.Write Item.Body
objOutputFile.Close
End Sub

' Utility function to tidy up filename
Function strLegalFileName(strFileNameIn As String) As String
Dim i As Integer
Const strIllegals = "\/|?*<>"":"
strLegalFileName = strFileNameIn
For i = 1 To Len(strIllegals)
strLegalFileName = Replace(strLegalFileName, Mid$(strIllegals, i, 1), "_")
Next i
End Function