BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools

SAS Add-In to MS Office: Refreshing the Data & Breaking the Link

Submitted by on 2012-02-09 – 7:51 AM 8 Comments

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:

  1. Click the check box for the data set where you want to break the link.
  2. Click the Remove Link icon on the toolbar.
  3. 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!”
  4. 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.

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: ,

8 Comments »

  • Niraj:

    In the View SAS Data window there is a button called Filter & Sort that allows you to setup a filter on the SAS data.
    Go to the Filter pane and select the Advanced Edit button.

    Based on the data – here are some ways you can setup the filter.

    date >= intnx(‘month’, today(), 0)

    You can learn more about the INTNX function in this SAS Global Forum Paper: Improve Your Dating: The INTNX Function Alignment Value SAMEDAY
    and also at SAS Community INTNX Function Examples.

    Hope this helps!
    Tricia

  • Niraj says:

    Hi,

    Just wondering if you would know how to change a filter in the Pivot table dynamically.

    Eg:

    I have applied a filter to view data only for the month of July. When the month changes to August how can I make the filter switch to August automatically. I know this might sound something silly but what I meant is if there is anything in the pivot table within filters to select the last or latest month’s data. Having a feature like that would save a lot of time for users who would have to go to multiple Pivot tables and change the filter manually from July to Aug.

    Thanks in advance!

  • If you mean “join” I don’t know a way except with a stored process that allows you to load the tabs and set the join key. Honestly – if you are advanced enough to realize you need to join tables … time for SAS Enterprise Guide! Get ready for the POWER!!!

    If I missed your point – let me know.

    Appreciate your comment!

  • Ksenia says:

    Speaking on refreshing SAS data in Excel 2010… I’ve been trying to figure out a way to link 2 different tabs in one excel file to 2 different SAS tables to no avail… Is it at all possible?

  • Good point. Just for MS Office posts or for all?

  • Jamie says:

    It might be helpful to indicate which versions of SAS and MSO you’re using for this — I work with some clients who do not have these features available to them.
    Jamie

  • And I actually proofread this post .. LOL!!!
    Next time I’ll add some coffee.
    Thanks.

  • Michelle Homes says:

    Just a typo I noticed but one you may want to change…

    In Breaking The Link To Your SAS Data section, step 3… It actually states “You can Undo this!” I think you meant to write “You can’t Undo this!”…

    🙂