® SAS Add-In 8 for Microsoft Office: User’s Guide SAS® Documentation February 21, 2023 The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2018. SAS® Add-In 8 for Microsoft Office: User’s Guide. Cary, NC: SAS Institute Inc. SAS® Add-In 8 for Microsoft Office: User’s Guide Copyright © 2018, SAS Institute Inc., Cary, NC, USA All Rights Reserved. Produced in the United States of America. For a hard copy book: No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the prior written permission of the publisher, SAS Institute Inc. For a web download or e-book: Your use of this publication shall be governed by the terms established by the vendor at the time you acquire this publication. The scanning, uploading, and distribution of this book via the Internet or any other means without the permission of the publisher is illegal and punishable by law. Please purchase only authorized electronic editions and do not participate in or encourage electronic piracy of copyrighted materials. Your support of others' rights is appreciated. U.S. Government License Rights; Restricted Rights: The Software and its documentation is commercial computer software developed at private expense and is provided with RESTRICTED RIGHTS to the United States Government. Use, duplication, or disclosure of the Software by the United States Government is subject to the license terms of this Agreement pursuant to, as applicable, FAR 12.212, DFAR 227.7202-1(a), DFAR 227.7202-3(a), and DFAR 227.7202-4, and, to the extent required under U.S. federal law, the minimum restricted rights as set out in FAR 52.227-19 (DEC 2007). If FAR 52.227-19 is applicable, this provision serves as notice under clause (c) thereof and no other notice is required to be affixed to the Software or documentation. The Government’s rights in Software and documentation shall be only those set forth in this Agreement. SAS Institute Inc., SAS Campus Drive, Cary, NC 27513-2414 February 2023 SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies. 8.0-P1:amoug Contents Chapter 1 / What’s New in SAS Add-In 8 for Microsoft Office . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 New SAS Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Integration with SAS Visual Analytics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 General Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Enhancements to Automation Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Chapter 2 / Introduction to the SAS Add-In for Microsoft Office . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 About the SAS Add-In for Microsoft Office . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 About SAS Visual Analytics Add-In for Office . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Determining Your SAS Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Check for Software Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Chapter 3 / Getting Started with the SAS Add-In for Microsoft Office . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Understanding Your Server Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Working with the SAS Metadata Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Working with SAS Visual Analytics Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Logging On to a Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Migrating SAS Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Managing Multiple Installations of the SAS Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Loading and Unloading the SAS Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Using the SAS Grid Computing Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Connecting to the SAS Viya Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Chapter 4 / Using the SAS Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Using the SAS Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 About the SAS Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Understanding Role-Based Settings in the SAS Add-In . . . . . . . . . . . . . . . . . . . . . . . 25 Chapter 5 / Customizing the SAS Add-In for Microsoft Office . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 About Customizing the SAS Add-In for Microsoft Office . . . . . . . . . . . . . . . . . . . . . . . 27 Setting the Data Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Setting the Results Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Customizing the Ribbon and the SAS Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Setting the Task Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Setting the Advanced Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Chapter 6 / Working with Microsoft Excel Data and SAS Data Sources . . . . . . . . . . . . . . . . . . . . . . . 57 About Accessing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Available Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Working with Data in a Microsoft Excel Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Working with Data in a PivotTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Filtering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Copy Data to a SAS Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Update Data on the SAS Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Working with Information Maps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Accessing Data in SAS Viya . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 iv Contents Chapter 7 / Working with OLAP Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Open an OLAP Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 About the OLAP Analyzer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Working with OLAP Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Filtering and Data in an OLAP Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Customizing the OLAP Analyzer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Viewing an ESRI Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Setting OLAP Analyzer Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Chapter 8 / Working with SAS Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Accessing Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Selecting a Data Source for the SAS Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Running Tasks on a Slice of a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 View the Input Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Creating Microsoft Excel Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Changing the Destination of the Output Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Viewing Output Data in Your Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 View Properties of a Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Working with Task Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Working with Custom Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 Chapter 9 / Working with Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 About Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Open a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Adding Comments to a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 View Display Rules for a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Working with Prompts in SAS Visual Analytics Reports . . . . . . . . . . . . . . . . . . . . . . 154 Create a Snapshot of a Report in Your Microsoft Office Document . . . . . . . . . . . . 155 View the Data Used to Create a Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Run a Stored Process to Generate a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Remove SAS Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Generating Reports from SAS Stored Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 Refreshing SAS Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Chapter 10 / Working with SAS Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 About the SAS Program Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Run a SAS Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Import a SAS Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Export SAS Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Editing Program Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Using the Syntax Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Keyboard Shortcuts in the Program Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Using Editor Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Creating and Saving a New Scheme . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Customizing the Editor Appearance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 Creating User-Defined Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Setting the Program Editor Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Chapter 11 / Working with Documents in SAS Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 About SAS Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 Save a Document to SAS Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 Open a Document from SAS Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 Specify the Security Settings for a Published Document . . . . . . . . . . . . . . . . . . . . . 185 Chapter 12 / Working with Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Viewing the Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Contents v Modify the Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Cutting, Pasting, and Refreshing Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Delete SAS Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Replicate SAS Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Save a Report as HTML or PDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 Export a SAS Visual Analytics Report to PDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 Applying Styles to the Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 Sending Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Troubleshooting: Working with Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Chapter 13 / Managing SAS Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Working with Favorites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 Managing Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Scheduling a Time to Refresh SAS Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 Remove SAS Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 Setting the Properties of an Analysis or Data Source . . . . . . . . . . . . . . . . . . . . . . . . 204 vi Contents 1 1 What’s New in SAS Add-In 8 for Microsoft Office Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 New SAS Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Integration with SAS Visual Analytics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 General Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Enhancements to Automation Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Overview The SAS Add-In 8 for Microsoft Office is available in Microsoft Excel, Microsoft Word, Microsoft PowerPoint, and Microsoft Outlook. Although some functionality is unique to a specific Microsoft Office application, most functionality is similar in each Microsoft application. Here are the new features and enhancements in this release: n a new user interface. For more information, see “New SAS Panel” on page 2. n integration with SAS Visual Analytics 8.3 and earlier releases. For more information, see “Integration with SAS Visual Analytics” on page 2. n general enhancements, such as the ability to customize the content on the Ribbon. For more information, see “General Enhancements” on page 3. n changes to the automation interface to reflect the new user interface. For more information, see “Enhancements to Automation Interface” on page 3. 2 Chapter 1 / What’s New in SAS Add-In 8 for Microsoft Office New SAS Panel The new SAS panel consolidates all of the SAS add-in functionality, so you can quickly access everything in one place. The SAS panel contains these tabs: n From the Home tab, you can view this information: o any sample SAS Visual Analytics reports from your SAS Demo server o any SAS content (such as data sources, reports, programs, and so on) that are currently open o any SAS content that you have recently opened o any tasks, reports, data sources, or stored processes that you have identified as favorites n From the Reports tab, you can see all your server connections and their current status. Use this tab to open reports in the SAS Add-In for Microsoft Office. n From the Tasks tab, you can open any tasks that are shipped with the SAS add- in, any custom tasks that you have created, and any SAS Studio tasks (if SAS Studio is licensed and installed at your site). n The Results tab shows all the reports that you have opened. This tab opens after you run a report, task, or stored process. You can interact with the report and select the report elements to include in the Office document. n The Programs tab includes a code editor that you can use to write and run SAS programs. From this tab, you can view the SAS log and results. You can also select the elements of your program to include in the Office document. n Use the Search tab to search all of the available servers for a report. Integration with SAS Visual Analytics The SAS add-in supports content from SAS Visual Analytics 8.3 and earlier releases. n The SAS Add-In for Microsoft Office is shipped with a default sample server. Sample reports from this server are available from the Home tab. If you remove a sample report or the sample server, click Restore Sample Reports in the SAS Add-In for Microsoft Office Options dialog box. For more information, see “Customizing the Ribbon and the SAS Panel” on page 49. n If guest access is available, you can now log on to a SAS Visual Analytics Server as a guest. For more information, see “Define Your SAS Visual Analytics Servers” on page 14. n When creating a server connection to a SAS Visual Analytics Server, you must include a description. By default, the description is userID@server. For more information, see “Define Your SAS Visual Analytics Servers” on page 14. Enhancements to Automation Interface 3 n The Server session locale option now specifies the locale for both the SAS Workspace Server and the SAS Visual Analytics Server. For more information, see “Setting the Advanced Options” on page 52. General Enhancements Here is the new and enhanced functionality for the SAS Add-In 8 for Microsoft Office: n The Automatically insert results into the Office document when opening a report option inserts the entire results into the current Office document when you first open the report. By default, the results are displayed only in the task pane. This option applies only when you are first opening a report. It does not apply when you refresh a report. For more information, see “Setting the Results Options” on page 33. n Use the Number of recent tasks to display in the task browser option to specify the maximum number of tasks to display in the task browser. The default value is 15. For more information, see “Setting the Task Options” on page 50. n You can now customize the menus on the SAS Ribbon. For more information, see “Customizing the Ribbon and the SAS Panel” on page 49. n Starting in this release, KPI gauges are not inserted as a single screen shot into the document by default. Instead, these gauges are displayed on the Results tab in the SAS panel. To change this behavior, select the Include all KPI gauges when inserting into a document option. For more information, see “Setting the Results Options” on page 33. n The SAS add-in now supports the EXCEL and POWERPOINT tagsets for the _ODSDEST= option, which is found in the code for many stored processes. Enhancements to Automation Interface For information about the automation interface, see SAS Add-In for Microsoft Office: Developer’s Guide. The AutoInsertResultsIntoDocument option inserts the entire results into the current Office document when you first open the report. By default, the results are displayed only in the task pane. This option applies only when you are first opening a report. It does not apply when you refresh a report. Starting in this release, the default value for the DisplayAllKPIGauges option is now False. KPI gauges are not inserted as a single screen shot into the document by default. Instead, these gauges are displayed on the Results tab in the SAS panel. Because of changes in the user interface for the SAS add-in, these options are now obsolete: 4 Chapter 1 / What’s New in SAS Add-In 8 for Microsoft Office n CloseTaskBrowserOnOpen n ShowPreviewChangesWindow n PreviewVisualAnalyticsReports n Word.InsertResultsIntoCurrentDoc n Word.RTFStyle