SAS Add-In to MS Office: Refreshing the Data & Breaking the Link
If you use SAS Add-In to Microsoft Office – specifically Excel, then you already know how awesome it is to link to the SAS data on the server. First, if you are using data that needs to be refreshed – just a click of the button brings you the new rows and your report or charts are updated immediately. Awesome! Let the analysis begin …
Refreshing Your MS Office Report with SAS Data
Using the SAS Add-In to MS Excel (or Word or PowerPoint), you can build charts from the SAS data sets. You may have a report that you create each month based on an Oracle table that is extracted into SAS. On the first day of each month the data is made available, which is great because you report is due the next day and your manager gets upset when it’s not on his desk. [He’s a fan of your work – what can you say?]
Let’s make your job a little easier so you can get back to answering your fan mail. Using the SAS Data button from the SAS ribbon, you can peruse the SAS Server to find the desired data. With the found data, you can setup filters and sorting – it’s quicker to sort 100,000 rows on the SAS Server than your laptop. You can pull the dataset directly into MS Excel worksheet ready to be counted, averaged, and otherwise manipulated.
Tip! Notice that you can export the data directly to a Excel pivot table.
In the past, you would have had to cut-and-paste or type the data (eek!). With the SAS connection, you can create the report once and then each month/week/day simply click the Refresh icon to have your new data! Of course – you should probably not tell your manager about this time saver so you can take an extra coffee break. (You are so naughty!! Must be why he loves you!)
Breaking the Link to the SAS Data
Perhaps when you distribute the report, you would prefer that others not be able to make modifications to the data. For instance, if others have the SAS Add-In for MS Office on their laptop, they could also click the Refresh icon and update the report. What you don’t want is for the reports to get out of sync or when you make changes, you want others to use your most recent report.
Create a copy of the report that you intent to distribute and use this technique on it. There is no reversing this process!
To break the link, click the Manage Content icon from the SAS ribbon (see figure above) and do the following:
- Click the check box for the data set where you want to break the link.
- Click the Remove Link icon on the toolbar.
- A SAS sanity check box appears warning you that once you do this – there’s no going back! You cannot ( 🙄 ) Undo this! Repeat after me, “This is forever!”
- Afterwards, the dataset is no longer appears in the Manage Content window. Poof … just like magic!
Notes: I’m using SAS BI 9.3 but these features were available in SAS BI 9.2 and Microsoft Office 2010.
Learn all about SAS BI
You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.
Latest posts by Tricia Aanderud (see all)
- Creating a Web Analytics Report in SAS Visual Analytics 8.1 - 2017-06-19
- Designing Dashboards: Sending Your Style Vibe - 2017-01-21
- SAS Visual Analytics: Design Versus Reality - 2016-10-05
- Seize the Day! Submit an #SASGF Abstract - 2016-09-21
- Need a Dynamic X-Axis with Your SAS Visual Analytics Report? - 2016-07-31