SAS® Add-In 7.1 for Microsoft Office: Getting Started in Microsoft Excel, Microsoft Word, and Microsoft PowerPoint, Second Edition SAS® Documentation The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2014. SAS® Add-In 7.1 for Microsoft Office: Getting Started in Microsoft Excel, Microsoft Word, and Microsoft PowerPoint, Second Edition. Cary, NC: SAS Institute Inc. SAS® Add-In 7.1 for Microsoft Office: Getting Started in Microsoft Excel, Microsoft Word, and Microsoft PowerPoint, Second Edition Copyright © 2014, 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 May 2015 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. P1:amogs Contents Using This Book v . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Accessibility Features of the SAS Add-In for Microsoft Office xi . . . . . . . . . Chapter 1 / Introduction to the SAS Add-In for Microsoft Office 1 . . . . . . . . . . . . . . . . . . . . . . What Is the SAS Add-In for Microsoft Office? 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . Benefits to Using the SAS Add-In for Microsoft Office 2 . . . . . . . . . . . . . . . . . . How to Use the SAS Add-In for Microsoft Office 5 . . . . . . . . . . . . . . . . . . . . . . . . Chapter 2 / Adding SAS Content to a Microsoft Excel Workbook 7 . . . . . . . . . . . . . . . . . . . . About the Tasks That You Will Perform 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . How to Access the Input Data Source 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Generate the Summary Statistics for the Federal Reserve Data 9 . . . . . . Apply a Style to the Summary Statistics Results 12 . . . . . . . . . . . . . . . . . . . . . . . Apply the Same Style to All Future Results 13 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding Results to an Existing Worksheet 14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Simulate the Federal Reserve Rate 17 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Display the Historical and Predicted Rate Values in a Line Plot 19 . . . . . Simulate the Federal Reserve Rates with the Click of a Button 23 . . . . . . Plot the Federal Reserve Rates over Time 29 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Chapter 3 / Adding SAS Content to a Microsoft Word Document 37 . . . . . . . . . . . . . . . . . . About the Tasks That You Will Perform 37 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Access to the Input Data Source 38 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Sort the Soccer Teams by Number of Wins and Conference 38 . . . . . . . . . Generate a Report 48 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Apply a Style to the Results 52 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Specify the Contents of the Report with a Click of a Button 58 . . . . . . . . . . Chapter 4 / Sending SAS Content from Microsoft Excel to Microsoft PowerPoint 71 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . About the Tasks That You Will Perform 71 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . How to Access the Input Data Source 72 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv Contents Drop Columns from the Input Data Source 73 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Plot the Total Criminal Offenses over Time 77 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Preview and Select the Results 84 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Determine the Correlation between Population and Violent Crime 84 . . Determine the Number of Violent Crimes by Year 88 . . . . . . . . . . . . . . . . . . . . . Send Results to a Microsoft PowerPoint Presentation 92 . . . . . . . . . . . . . . . . Refresh Results in the PowerPoint Presentation 94 . . . . . . . . . . . . . . . . . . . . . . . Chapter 5 / Editing and Refreshing SAS Content in Microsoft Excel and Microsoft Word 99 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . About the Tasks That You Will Perform 99 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Access to the Input Data Source 100 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Save the College Sample to a Local Directory 100 . . . . . . . . . . . . . . . . . . . . . . . . Edit the Tuition Costs in Microsoft Excel 100 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Refresh the Tuition Data in the Pamphlet 103 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Recommended Reading 107 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Glossary 109 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Index 111 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Using This Book Audience This book is intended for users of Microsoft Office 2007, 2010, or 2013. Each chapter in this book explains how to re-create a sample that is available from your installation directory. (For more information, see “Access to Samples and Input Data Sources” on page ix.) Samples are available for Microsoft Excel, Microsoft Word, and Microsoft PowerPoint. By re-creating these samples, you learn how to perform these tasks using the SAS Add-In for Microsoft Office: n run a SAS analysis and include the results in an Excel worksheet, Word document, or PowerPoint presentation n use Visual Basic code to refresh your content n preview the results from a SAS analysis and select the results that you want to include in the output n apply a custom style to your results n edit SAS data in Microsoft Excel n send content from one Microsoft Office application to another vi Using This Book Prerequisites for This Scenario Software and Configuration Requirements When you are ready to perform the tasks in this book, administrators at your site should have installed and configured all necessary components of the SAS Enterprise Intelligence Platform. You must also have the following software and permissions set up: n Microsoft Office 2007, 2010, or 2013 installed on your desktop. You can use either 32-bit or 64-bit versions of Microsoft Office 2010 and 2013. n permissions to access the SAS Metadata Server. SAS support personnel at your site set these permissions and provide the name and location of the SAS Metadata Server. n a metadata configuration file to specify which SAS Metadata Server you will connect to by default. This file enables you to access SAS data sources on remote servers. For more information about setting up a metadata configuration file, see the online Help for the SAS Add-In for Microsoft Office. Enable the SAS Add-In in Microsoft Excel, Word, and PowerPoint To create the examples in this document, the SAS Add-In 7.1 for Microsoft Office must be available in Excel, Word, and PowerPoint. If the SAS add-in is enabled in a specific Microsoft Office application, a SAS tab appears in the Ribbon for that Microsoft Office application. If the SAS tab is not available, contact the SAS support personnel at your site. An administrator might need to load the SAS add-in in a Microsoft Office application or use the SAS Add-In for Microsoft Office Switcher Utility to enable the SAS add-in in a Microsoft Office application. For more information about loading the SAS add-in or the SAS Add-In for Microsoft Office Switcher Utility, see the online Help for the SAS Add-In for Microsoft Office. Set Your Microsoft Office Documents to Support Macro-Enabled Content vii Set Your Microsoft Office Documents to Support Macro-Enabled Content The samples that you create in this book include macro-enabled content. When you save your Microsoft Office documents, you must use the file extensions for macro- enabled content. These file extensions are .xlsm in Excel, .docm in Word, and .pptm in PowerPoint. When you open a Microsoft Office document that includes macro-enabled content, you want to be notified that this content is disabled. To receive these notifications when you open macro-enabled content in Microsoft Excel, Word, or PowerPoint: 1 In Microsoft Office 2013 or 2010, select File  Options. In Microsoft Office 2007, click and select product-name Options, where product-name is Excel, Word, or PowerPoint. The product-name Options dialog box appears, where product-name is Excel, Word, or PowerPoint. 2 In the selection pane, click Trust Center. The Trust Center panel appears. 3 Click Trust Center Settings. The Trust Center dialog box appears. 4 In the selection pane, click Macro Settings. The Macro Settings panel appears. 5 Select Disable all macros with notification, and then click OK. 6 Click OK to close the product-name Options dialog box. 7 Close and restart Microsoft Excel, Word, or PowerPoint to activate this option. Now, when you open a Microsoft Office document that contains macro-enabled content, you are warned that some active content has been disabled. To enable the content in your Microsoft Office document: viii Using This Book 1 In the warning message, click Options. The Microsoft Office Security Options dialog box appears. 2 Select Enable this content, and then click OK. Configure Your Server Connection You must configure the connection between the SAS Add-In for Microsoft Office and a SAS Metadata Server in order to copy the data for the Word and PowerPoint examples in this book. This connection is called a profile. To configure your server connection: 1 In the Ribbon, click the SAS tab. In the Tools group, click Tools, and then select Connections. The Connections window appears. Note: If you are running SAS Add-In 7.14 for Microsoft Office or later, the SAS Connections window appears. Click Manage to open the Connections window. 2 In the selection pane, click Profiles to open the Profiles panel. From this panel, select a profile and click Set Active. If no profiles are available, see the online Help for the SAS Add-In for Microsoft Office to learn how to create a profile. Note: If you do not use a profile, then you can access only the SAS server that is installed on your local machine. You cannot define libraries or share SAS resources with other SAS applications. 3 In the selection pane, click Servers to open the Servers panel. From the drop-down list, select the default server for the active profile. TIP To complete the Word and PowerPoint tasks, you copy data from an Excel workbook to your default server. To access these data sources, you need to know Access to Samples and Input Data Sources ix the default server for your active profile. This book assumes that your default server is SASApp. 4 Click Close to close the Connections window. Access to Samples and Input Data Sources The samples that are used in this book are available from SAS-installation- directory\SAS\AddinforMicrosoftOffice\7.1\Samples. For the Microsoft Excel samples, the input data for the SAS content is included in the Excel workbook. Because data sources cannot be saved in Word or PowerPoint, the data for these samples is saved in the RunFirst.xslm workbook. You must copy this data to a SAS server. After a data source is copied to a SAS server, the data source becomes a SAS data set, and you can use it like any other SAS data source. To copy the data for the Word and PowerPoint samples to your SAS server: 1 Open Microsoft Excel and verify that the SAS tab is available in the Ribbon. The SAS add-in must be available in Microsoft Excel for you to copy the Excel data to the SAS server. If the SAS tab is not available, see “Enable the SAS Add-In in Microsoft Excel, Word, and PowerPoint” on page vi. 2 Open the RunFirst.xlsm workbook. Note: The RunFirst.xlsm workbook contains some macro-enabled content. To copy the data from the workbook to a SAS server, you must enable the document for macro-enabled content. For more information, see “Set Your Microsoft Office Documents to Support Macro-Enabled Content” on page vii. 3 In the Run First! worksheet, click Copy to Server. 4 To verify that all of the data was copied to your SAS server, select Manage Content on the SAS tab. The Manage Content window appears and lists the seven data sets that were created on the SAS server. x Using This Book TIP The names of these data sets use the following format: server-name:library- name_data-set-name. For the examples in this book, the default server is SASApp and the library is SASData. 5 Click Close to close the Manage Content window. 6 Close the RunFirst.xlsm workbook in Microsoft Excel.