Using Microsoft Excel PivotTables in the SAS® Add-In for Microsoft Office
Did you miss the Ask the Expert session on Using Microsoft Excel PivotTables in the SAS® Add-In for Microsoft Office? Not to worry, you can catch it on-demand at your leisure.
With SAS Add-In for Microsoft Office, you can experience the breadth and power of SAS within familiar Microsoft environments.
In this webinar, you’ll learn how the SAS add-in functionality enables SAS data to be explored using an MS Excel pivot table.
For example, one technique shown will be how to change the pivot table report design to accommodate the use of values as inputs to SAS tasks.
Here are the questions from Q&A segment held at the end of the session.
Does the data source for the add in need to be a SAS server? Or can we just use processed SAS data sets as a source?
You can open a SAS data source from your local file system. However, there is a limitation in that any date or time values are not converted to Microsoft Excel date and time values. Therefore, if you open a SAS data set from your local file system, all date and time fields that are added to the PivotTable report will have meaningless values. If you open the SAS data set from a server, the date and time values will be converted when opening the data set into a PivotTable report.
If the SAS dataset gets disconnected or not available to the Excel WorkBook, will the Workbook keep a cached version of the SAS data? Will the pivot table still be able to be visualized?
The PivotTable report is visualized. However, without a connection to the server, you are not able to interact with the SAS data.
Where would we find a listing of tasks?
Use the online Help to find a listing of task. Select the Contents tab and expand Analyzing Data, then Running SAS Tasks. One of the selections in that category is SAS Tasks.
If I create a pivot weekly for a report can I save the task and execute with the new data each week?
Yes. If the task results are based on the PivotTable report data, you would just need to refresh the results. Be careful that the data in the PivotTable is the same format that was used when first running the task.
Can you load SAS data sets into the excel data model?
I could not find a way to accomplish this from the SAS Add-In for Microsoft Office. When I selected, Data > Get External Data, I was able to select "Open SAS Data". This action opened the View SAS Data window, just as if I clicked the SAS Data icon on the SAS tab. As you saw in the demonstration, in that window, you are only able to select a single SAS data source. Actually, an Excel Data Model sounds very like what the Platform does with an Information Map (integrating data from multiple tables – effectively building a relational data source with a SAS information map and allowing you to get one tabular file that can be used in the SAS Add-in for Microsoft office or other BI client applications).
Recommended Resources
Course: SAS Office Analytics: Getting Started
Course: SAS Office Analytics: Fast Track
Book: Building Business Intelligence Using SAS®: Content Development Examples
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. To subscribe, select Subscribe from the Options drop down button above the articles.
