In her role as Product Manager for SAS Platform Technologies (including the SAS Add-In for Microsoft Office), my colleague Amy Peters hears this question often. With many organizations adopting Microsoft Office 365 -- the "cloud" version of Office -- what does this mean for other processes that integrate with Microsoft Office applications? Good news: SAS has a specific offering called SAS for Microsoft 365 that allows you to add SAS content to your favorite Microsoft Office clients. It's part of SAS Visual Analytics on SAS Viya.
Microsoft has used different names for these similar offerings: Office 2016, Office 365, Microsoft 365, Office Online. The bottom line is that most users of a "365" package in the cloud, also have access to the Microsoft Office tools on their Windows desktop. They can use the full version of Excel, PowerPoint, Word, etc., and they also have access to these same tools via a web browser. At SAS, we recently experienced this transition ourselves. Have the Office applications on our desktops vanished? No, they have not. While more of our data is now on the cloud (looking at you, OneDrive), it's not really changing how we work, especially when creating/maintaining content. (Like many organizations, we already had one foot in this world by using Microsoft SharePoint for collaboration.)
Collaboration on the web. Full control on the desktop
Let's look at an example of how I use SAS with Microsoft Office. First, I create a report in SAS Visual Analytics. Then I open Excel on my desktop and use the SAS Add-In for Microsoft Office to embed the shared report into my spreadsheet. Want to see what that looks like in action? Check out this video Tech Talk with SAS developer Tim Beese.
Now suppose that I share this content in Microsoft OneDrive, and my colleague views it in Excel in a web browser. Yep, the content is still there. The difference is that the content is not dynamic like it is on my desktop. So what do you do when you want to edit that spreadsheet displaying in the browser? You select Open in Excel and the document opens on your desktop. Voila! The content is dynamic and you have all the functionality the SAS Add-In for Microsoft Office provides.
How is Microsoft Office 365 changing your workflow?
Today, the expectation of most users working with "Office Online" applications in their browsers is that it's primarily for viewing and basic editing. Will this change? Probably. We're researching how to provide more of the SAS Add-In for Microsoft Office function in a browser app. If you or your colleagues need this browser-based function – you want to do something specific in Excel with your SAS content -- we want to hear from you. And do you have a plan to move completely to browser-based Office apps? Currently you can't create SAS content from a browser-based Office app. If that's a pressing need, we would like to know. For now, we're not hearing of use cases where some form of the desktop app isn't still in the picture.
SAS integration with these everyday productivity tools, like Microsoft Office, is important to us. Don't forget about these SAS programming methods to create and read your Microsoft Office content:
- At the data level, with PROC IMPORT, PROC EXPORT and LIBNAME XSLX to read and write Microsoft Excel files.
- Using ODS to create SAS report output with ODS POWERPOINT, ODS EXCEL, and now ODS WORD documents (preproduction) without the need for Microsoft Office software (so it works on Windows and Unix systems).
- Using Microsoft Office 365 APIs and PROC HTTP to read, create, and manage content in Microsoft OneDrive directly.
How are you using Microsoft Office 365 with SAS? How do you think this workflow will change for you in the next year or two? Leave a comment -- we would love to hear from you.
14 Comments
Good information Chris,
Thank to put it together.
We are you using Microsoft Office 365 with SAS and it is good going.
Hi Chris,
I am currently working on a SAS migration from On-Premise infrastructure to Cloud environment (GCP).
My target is to remove fat client (SAS Enterprise Guide, Add-In MS Excel), and replace it by thin client.
Regarding SAS Enterprise Guide, it represents a quite simple change --> SAS Studio usage
But, for my Add-In users, it is not feasable cause some users had developped VBA macros with SAS Stored Process call :
Excel gives parameters as input of Stored Process, which executes SAS program and provide outputs to Excel.
Would you thing that a short term workaround in Office 365 is planned for this use-case ?
Best regards,
Gaetan
By removing all local client apps, you remove the ability to easily run a Stored Process in an automated way. You can use the Stored Process web app to continue to provide access, or wrap these in a SAS BI Web Service -- but you will still need to then write some code to call that.
Chris,
I have a track open with SAS technical support regarding an error my user is getting when opening Excel 365 after we installed SAS Add-in for Microsoft Office.
SAS Add-in for Microsoft Office causing error when Excel opens. I have uploaded the error text to the track. The assigned support person doesn't seem to grasp that 365 is not Office 2013 and has not read the description of the problem or looked at the SAS Note.
I have tried installing the add-in using our SASDEPOT media and the 7.15 standalone installation media. The user has Office 365 and I saw a SAS Note 53764 http://support.sas.com/kb/53/764.html which mentions "If your version of Office is installed from Office 365, contact SAS Technical Support here and mention this SAS Note." My user also has the IBM Planning Analytics software installed (see png file). I don't know if there might be a conflict.
Have you seen anything like this before?
I read your track. The error you see might be related to bitness of MS Office vs. the SAS Add-In. Make sure you're running the same -- 32-bit Office and 32-bit SAS Add-In, or else 64-bit version of both.
I have a user who has been running a SAS program that uses PROC EXPORT to convert a SAS dataset to .XLSB. Because her SAS version is 64-bit SAS 9.4 and her Office version WAS 32-bit Office 2013, she had to use the 32-bit PC File Server to export the file by using the appropriate DBMS option. This program has worked fine for many years. This week, our ITS has "upgraded" her Office 2013 to Office 365, and suddenly the program does not work. The Excel version in Office 365 is apparently still 32-bit, since the Account page from the File Tab does not say that it is 64-bit. It should NOT be necessary for her to use 64-bit Excel (almost nobody in the world DOES use 64-bit Excel, and Microsoft admits openly that almost nobody needs to.)
Is SAS PC File Server compatible with Office 365?
Hi John, what do you mean by "program doesn't work"? Is there an error in creating the XLSB file, or does Excel just not recognize it? I suggest you work with SAS Tech Support to resolve this. The SAS PC Files Server does not use Excel directly, but uses underlying components to create the Excel-native files.
I have this same problem. We installed Windows 10 and Office 365 and now PC SAS 9.4 is not importing xlsx files with PROC IMPORT. Excel in Office 365 is 64 bit. What do I need to do to fix this?
I recommend using DBMS=XLSX to read Excel files in SAS -- should not depend on Excel bit architecture at all.
We've run into the same issue with but with Office 2019. After working with SAS support, it appears that Office 2019 sets different registry keys than the ones that PC Files server is looking for... So even though office is installed (and the bit-ness matches), sas still gives the "ERROR: Connect: Class not registered" in the log when trying to use the libname excel engine.
I am also having problems. If I use the menus with Import data and work through the dialog boxes, SAS can find the file and import my excel file. When I check that I want it to save the IMPORT statements so I can run it again, it does, but the saved code does not work. The 365 adds on the One Drive part of the path to the path for the imported file and SAS cannot find that file when I run it again. It also can't find it if I remove the one drive part. The LIBNAME statement is not able to find a directory whether the directory is written as the IMPORT statement said or just use the path that I use with the import menu dialog. My machine is 64 bit and I just updated my SAS with a 9.4 64 bit version.
I selected c:\users\ml371291\grant\data\Feb1653jtALL_JustData.xlsx for the import dialog, but it was saved as:
PROC IMPORT OUT= WORK.jtdata
DATAFILE= "C:\Users\ml371291\OneDrive - Western New England
University\Desktop\Grant\Data\Feb1653jtALL_JustData.xlsx"
DBMS=EXCEL REPLACE;
It cannot find that file with the OneDrive... in it or with the original path. It will not accept the
C:\Users\ml371291\OneDrive - Western New England University\Desktop\Grant\Data
directory for the libname or an export command.
University\Desktop\Grant\Data\
Hi Ronald,
Clearly these are two different paths. I'm not sure what's happening to "switch" the location on you here, unless one path is a symbolic link to the other, and perhaps SAS cannot process the symbolic link. I suggest that you open a track with SAS Tech Support. In the meantime, you can play with the FILENAME statement to see if it can resolve these two paths you specified. If so, you can use a fileref in the DATAFILE= option.
I am looking for any articles/books you have written on accessing SAS data (read or write) using VBA in Excel. I don't have a local SAS instance, but I have access on the server and I have the Excel SAS add-in. I looked at all your books but the closest I could find was one about .Net. Unfortunately, at the moment, I have to try to use VBA. I've read https://support.sas.com/rnd/itech/doc/dist-obj/winclnt/winvbpro.html but it seems focused on a local SAS instance and I get 'class not registered' when trying to run their sample code. I've posted a question on the SAS community forum and was basically referred to you as the resident expert who would know if anyone does. Can you please help point me in the right direction?
I don't have many VBA examples but I do have some in PowerShell -- perhaps these can help you get started.