SAS crash with Windows 10 and Office 365

[AndrewZ]

Our company upgraded from Windows 7 and Office 2013 to Windows 10 and Office 365. Since then, SAS 9.4 (m3, m4, and m5) predictably crash when using LIBNAME ODBC with Excel and sending an email through Outlook (MAPI).The SAS window quickly closes, and sometimes there is a popup window with a brief message.

I have had a SAS bug ticket open for two months, but after trying many things, we have made no significant progress.

Does this code crash for you? (The Excel file may need to be a valid path.) Do you have any insight?

%put NOTE: Setting up FILENAME for Outlook (MAPI);
filename myemail
    email ("test@example.com")
    subject="test"
    debug
    ;

%put NOTE: Sending email;
data _null_;
    file myemail;
    put "hello";
run;


%put NOTE: Connecting to Excel;
libname excel
    odbc
    autocommit=yes
    required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=c:\temp\excel.xlsm;"
    ;

%put NOTE: Closing connection to Excel;
libname excel;

Some things we have tried

• Repair Office 365: SAS still crashes
• Reinstall .NET 4.7.1: SAS still crashes
• Gather logs from Windows Event Viewer and SAS
• Reverse the order of ODBC and email: SAS crashes in a different way
• Boot into safe mode: Office will not start, and SAS crashes more easily
• Call SLEEP() between the steps: SAS still crashes
• Use SMTP instead of MAPI email: SAS does not crash
• Use Windows 7 and Office 2013: SAS does not crash

[ballardw]

Do you have SAS/Access Interface to PC Files available so that you do not need ODBC to have your Excel files referenced in a LIBNAME statement? Have you investigated that as a possible option?

It isn't at all obvious what you may be doing with that connection to Excel. Are you reading or writing? And if writing what does the email part have to do with the process?

[AndrewZ]

ballardw,

Thanks for the quick reply.

No, we do not use SAS/Access Interface to PC Files. Instead, we use the ODBC connection. We do not want to buy another SAS license, and it worked fine with ODBC before.

In the real-world code, I read information from an Excel spreadsheet, process it, and send an email. In the toy example that I posted here (that is still sufficient to crash SAS), it does not read nor write from Excel: just opening the ODBC connection to Excel is sufficient to crash SAS.

[ChrisHemedinger]

Interesting that using SMTP instead of MAPI helps -- any reason that you can't adopt that?  MAPI libraries are notoriously tricky and might be in conflict with other DLLs that are loaded.  Bitness is also a factor -- MAPI is 32-bit if you have 32-bit Office, but your SAS is probably 64-bit, right?

When you use ODBC and MAPI from SAS, both sets of DLLs are loaded into the SAS process.  A crash in one of those will crash your SAS process.  Only the traceback info will help show where this is happening.  Removing MAPI from the equation might make a more maintainable approach going forward.

[AndrewZ]

IT does allow SMTP only from servers, not from laptops. We normally develop SAS code on laptops. I am not sure whether they would make an exception.

The bitness matches: SAS 32-bit, Excel 32-bit, Outlook 32-bit, and 32-bit ODBC driver.

It was difficult because the SAS windows disappears in less than 1 second, but I got traceback info from the SAS log.

[ChrisHemedinger]

Have you tried to run the program in batch -- that is, save to a SAS file and just run with SAS.EXE on the command line?  That might eliminate the windows interactions.

SMTP with Office 365 should be possible using a technique similar to what I've outlined for Gmail.  Can be secure and authenticated, which may be a comfort to your IT staff.

[AndrewZ]

Chris, I had not tried batch mode, but I just did. It crashed, but this is a much cleaner, easier way to get the traceback. I will send this log to SAS support. If SAS support cannot figure out MAPI, I will ask IT about SMTP.