SAS Office Analytics: Working with Your Inner SAS Programmer

When working with new or even casual SAS BI users, the SAS Office Analytics really bridges the gap between “I’m not a SAS programmer” and “I have 1000’s of ideas for data visualizations”. Users need an easy way to access the data warehouse and create reports or answer simple questions. The SAS Office Analytics package (aka SAS Add-In for Microsoft Office) a natural choice since it allows the inner SAS programmer to express itself even without any programming skills!2014-03-two sides of face

How does SAS Office Analytics Work?

SAS Add-In for Microsoft Office uses the Microsoft COM add-in technology to extend the data access, reporting, and analytic capabilities of Microsoft Office suite. Once the SAS Add-In is installed, an integrated SAS toolbar or ribbon is available in the Office environment as shown below.

sas office analytics
Click for larger image


Users must have a metadata user ID with appropriate permissions before they can access the data. The permissions are set through the same connection profile that SAS Enterprise Guide uses – however users don’t have to have SAS EG installed on their computer.  But it does mean that multiple connections to different SAS metadata servers. [More information about administering the tool here. ]

With the 6.1 release, a stand-alone installer is supported making it easier to install over a distributed deployment, especially with provisioning tools such as Microsoft System Center Configuration Manager (SCCM). [More here]

Integration with MS Office

Each MS Office application offers the functionality in different ways. The following table describes the capabilities of each application. Excel has the most integration with the SAS metadata server and allows users the most interaction. However notice that SAS has also added the ability to access the SAS Visual Analytics reports and explorations.

sas office analytics integration with ms office

Many users may not realize how large some of the datasets they want to access can be.  The add-in allows the users to complete the analysis on the high powered SAS servers and return the results to their local machine.  It’s much quicker.

Ideas for Use

During my SAS Global Forum talk, I’ll go over the features and some ideas for how to use the tool and extend its functionality.  Here’s a preview of some of the other add-in features.

Here’s a report that was created in MS Word and the graphics were added using the Tasks menu.  Each task guided me through a wizard (just like SAS Enterprise Guide) so I wasn’t left on my own to guess what to do.  There are over 80 tasks available ranging from visualizations like bubble plots to sophisticated analytics tasks such as regression analysis.

sas office analytics with ms word
Click for larger image

The best part is that when the data is updated on the server, the user clicks the Refresh button to have a new report for analysis. And these charts are easily shipped over to MS PowerPoint in case the person is giving a presentation about the report.

SAS Office analytics send to powerpoint
Click for larger image

Users can also download data directly into a pivot table or build charts from the SAS data.  It’s feature packed!

Implementing Self Service BI?

This package assists with implementing self service BI in your organization, however it’s not as easy as sending the link to install to the users.  During the SAS Global Forum 2014 presentation called Tips for Using SAS Add In for MS Office, I’ll give you some hints for making the tool introduction a success in your organization.

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.

Learn More about SAS Stored Processes and Prompts

You can learn more tips and tricks for creating, debugging, and using SAS stored processes in the 50 Keys to Learning SAS Stored Processes book. It's a complete guide to SAS stored processes. Check Amazon for best pricing and quick shipping!