BookmarkSubscribeRSS Feed
AndrewZ
Quartz | Level 8

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

 

6 REPLIES 6
ballardw
Super User

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
Quartz | Level 8

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
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
AndrewZ
Quartz | Level 8

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
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
AndrewZ
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2931 views
  • 0 likes
  • 3 in conversation