Exchanging Data between SAS and Microsoft Excel

Exchanging Data between SAS and Microsoft Excel

by William E. Benjamin
Released April 2015
Publisher(s): SAS Institute
ISBN: 9781629596907

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master simple-to-complex techniques for transporting and managing data between SAS and Excel
William Benjamin's Exchanging Data between SAS and Microsoft Excel: Tips and Techniques to Transfer and Manage Data More Efficiently describes many of the options and methods that enable a SAS programmer to transport data between SAS and Excel. The book includes examples that all levels of SAS and Excel users can apply to their everyday programming tasks. Because the book makes no assumptions about the skill levels of either SAS or Excel users, it has a wide-ranging application, providing detailed instructions about how to apply the techniques shown. It contains sections that gather instructional and syntactical information together that are otherwise widely dispersed, and it provides detailed examples about how to apply the software to everyday applications. These examples enable novice users and power developers alike the chance to expand their capabilities and enhance their skillsets.
By moving from simple-to-complex applications and examples, the layout of the book allows it to be used as both a training and a reference tool. Excel users and SAS programmers are presented with tools that will assist in the integration of SAS and Excel processes in order to automate reporting and programming interfaces. This enables programming staff to request their own reports or processes and, in turn, support a much larger community.

Table of contents

  1. Preface
  2. About This Book
  3. About The Author
  4. Chapter 1: Easy Data Movement between SAS and Microsoft Excel
    1. 1.1 Introduction
    2. 1.2 Examination of Excel Files
      1. 1.2.1 Purpose
      2. 1.2.2 Excel Data Types
      3. 1.2.3 General Excel Workbook Limitations
      4. 1.2.4 Excel Workbook Formatting Groups
      5. 1.2.5 Excel Data Ranges
    3. 1.3 Examples of Copy-and-Paste Techniques
      1. 1.3.1 Highlight, “Cut” or “Copy,” and Then “Paste”
      2. 1.3.2 Convert Text Data to Excel Column Data Fields
      3. 1.3.3 Copy Data to the SAS Enhanced Editor Window for Use in a SAS Program
      4. 1.3.4 Save Multiple Lines of Text in a Single Excel Cell
      5. 1.3.5 Converting Excel Tables to Text
    4. 1.4 Accessing Excel Data from the SAS Explorer Window and Toolbar
      1. 1.4.1 SAS Explorer Window and Toolbar Processing Method Descriptions
      2. 1.4.2 Picking the Export Wizard from the SAS Explorer Window “Export” Menu
      3. 1.4.3 Using the “Copy Contents to Clipboard” Option of the SAS Explorer Window
      4. 1.4.4 Selecting the “Save as Html” Option of the SAS Explorer Window
      5. 1.4.5 Using the “View in Excel” Option to Copy Data to Excel Files via HTML
      6. 1.4.6 SAS Toolbar File Option, the Gateway to the SAS Export / Import Wizards
      7. 1.4.7 Choosing the “Export Data” SAS Toolbar (Export Wizard) File Option
      8. 1.4.8 Electing the “Import Data” SAS Toolbar (Import Wizard) File Option
      9. 1.4.9 Using the Export / Import Wizards in a 32/64-Bit Mixed Environment
    5. 1.5 Chapter Summary
  5. Chapter 2: Use PROC EXPORT to Write SAS Data to External Files and Excel Workbooks
    1. 2.1 Introduction
    2. 2.2 Purpose
    3. 2.3 Syntax of the SAS EXPORT Procedure
    4. 2.4 Data Access Methods for Excel Files Supported by PROC EXPORT
    5. 2.5 Overview of the Examples
    6. 2.6 List of Examples
      1. Example 2.1 SAS Code to Export Data to an Excel 4 or Excel 5 Format File
      2. Example 2.2 PROC EXPORT Using the DBMS=DLM Option
      3. Example 2.3 PROC EXPORT Using the DBMS=EXCEL Option
      4. Example 2.4 PROC EXPORT Using the DBMS=EXCELCS Option
      5. Example 2.5 SAS Code to Export Data to an Excel File with No Column Headers
      6. Example 2.6 SAS Code to Export Data to a Network Windows Computer
    7. 2.7 Conclusion
  6. Chapter 3: Use PROC IMPORT to Read External Data Files and Excel Workbooks into SAS
    1. 3.1 Introduction
    2. 3.2 Purpose
    3. 3.3 Syntax of the SAS IMPORT Procedure
    4. 3.4 Data Access Methods for Files Supported by PROC IMPORT
    5. 3.5 Overview of the Examples
    6. 3.6 List of Examples
      1. Example 3.1 PROC IMPORT Using the DBMS=EXCEL4 or EXCEL5 Option
      2. Example 3.2 PROC IMPORT Using the DBMS=DLM Option
      3. Example 3.3 PROC IMPORT Using the DBMS=EXCEL Option
      4. Example 3.4 PROC IMPORT Using the DBMS=EXCELCS Option
      5. Example 3.5 PROC IMPORT Using the DBMS=XLS or XLSX to Select Columns
      6. Example 3.6 PROC IMPORT Using the DBMS=XLS or XLSX to Select Rows
      7. Example 3.7 PROC IMPORT Using the DBMS=XLS or XLSX to Select Excel Ranges
    7. 3.7 Conclusion
  7. Chapter 4: Using the SAS LIBNAME to Process Excel Files
    1. 4.1 Introduction
    2. 4.2 Purpose
    3. 4.3 Excel-Specific Features of the SAS LIBNAME Statement
    4. 4.4 Syntax of the SAS LIBNAME Statement
    5. 4.5 LIBNAME Statement ENGINE CONNECTION OPTION Descriptions
      1. 4.5.1 HEADER Option to Read Variable Names
      2. 4.5.2 MIXED Option to Select Data Types
      3. 4.5.3 PATH Option to Define Physical File Locations
      4. 4.5.4 VERSION Option to Identify Excel File Version
      5. 4.5.5 PROMPT Option to Interactively Assign a Libref
      6. 4.5.6 Other Common SAS PC File LIBNAME Options
    6. 4.6 Excel-Specific Dataset Options
    7. 4.7 UNIX, LINUX, and 64-Bit Windows Connection Options
    8. 4.8 Overview of the Examples
      1. 4.8 List of Examples
    9. 4.9 Examples
      1. Example 4.1 Using the Engine Connection HEADER Option
      2. Example 4.2 Using the Engine Connection MIXED Option
      3. Example 4.3 Using the Engine Connection PATH Option
      4. Example 4.4 Using the Engine Connection VERSION Option
      5. Example 4.5 Using Named Literals with the LIBNAME Statement
      6. Example 4.6 Using PROC CONTENTS to Examine an Excel Workbook
      7. Example 4.7 Using Dataset Options to Process Date and Time Values
      8. Example 4.8 Using Dataset Options to Process Variable Type Conversions
      9. Example 4.9 Processing on 64-Bit Operating Systems
    10. 4.10 Conclusion
  8. Chapter 5: SAS Enterprise Guide Methods and Examples
    1. 5.1 Introduction
    2. 5.2 Purpose
    3. 5.3 Typical Methods to Access Excel from SAS Enterprise Guide
    4. 5.4 Overview of the Examples
    5. 5.5 List of Examples
    6. 5.6 Examples
      1. Example 5.1 Using the Export Method with Enterprise Guide
      2. Example 5.2 Using the “Send To” Method
      3. Example 5.3 Using the “Send To” Method to Output a Graph or Report
      4. Example 5.4 Using the “Export” Method to Output a Graph or Report
      5. Example 5.5 Using “Open” or “Import” Toolbar Options to Read Excel Workbooks
      6. Example 5.6 Using the “Import Data” Toolbar Option to Read a Range of Cells
    7. 5.7 Conclusion
  9. Chapter 6: Using JMP to Share Data with Excel
    1. 6.1 Introduction
    2. 6.2 Purpose
    3. 6.3 Methods of Sharing Data between JMP and Excel
    4. 6.4 List of Examples
    5. 6.5 Examples
      1. Example 6.1 Within Excel, Set the JMP Preferences for Loading Excel Data
      2. Example 6.2 Reading Data from Excel to JMP
      3. Example 6.3 Writing Data from JMP to Excel
    6. 6.6 Conclusion
  10. Chapter 7: SAS Add-In for Microsoft Office (Excel)
    1. 7.1 Introduction
    2. 7.2 Purpose
    3. 7.3 Methods of Sharing Data Using SAS Add-In for Microsoft Office
    4. 7.4 List of Examples
    5. 7.5 Examples
      1. Example 7.1 Open a SAS Dataset Using SAS Add-In for Microsoft Office
      2. Example 7.2 Open a SAS Report Dataset (*.srx) Using SAS Add-In for Microsoft Office
    6. 7.6 Conclusion
  11. Chapter 8: Creating Output Files with ODS for Use by Excel
    1. 8.1 Introduction
    2. 8.2 Purpose
    3. 8.3 An Introduction to SAS Tagset Templates That Create Files for Excel
      1. 8.3.1 How to Locate a Tagset Template
    4. 8.4 Difference Between an ODS Tagset and an ODS Destination
    5. 8.5 Syntax of the ODS CSV and CSVALL Output Processes
    6. 8.6 CSV and CSVALL Tagset Options
    7. 8.7 Overview of CSV and CSVALL Examples
    8. 8.8 CSV and CSVALL Examples to Write *.csv Files
      1. Example 8.8.1 Simple CSV and CSVALL File Default Output Differences
      2. Example 8.8.2 CSV and CSVALL Title and Footnote Output Differences
      3. Example 8.8.3 Write Currency Values as Unformatted Numbers
      4. Example 8.8.4 Change Delimiters When Outputting Data with CSV Tagset
      5. Example 8.8.5 Save Leading Zeroes in Character Fields Sent to Excel
    9. 8.9 Syntax of ODS MSOFFICE2K Output Processes to Write HTML Files
    10. 8.10 MSOFFICE2K Tagset Template Options
    11. 8.11 Overview of MSOFFICE2K Examples
    12. 8.12 MSOFFICE2K Examples to Write HTML Files
      1. Example 8.12.1 Generating an HTML Output File with No Options
      2. Example 8.12.2 Generating an HTML File Using the Summary_Vars Option
    13. 8.13 Syntax of the ODS EXCELXP Tagset Template Output Processes
    14. 8.14 ODS EXCELXP Tagset Options
    15. 8.15 Overview of EXCELXP Examples
    16. 8.16 EXCELXP Examples to Write XML Files
      1. Example 8.16.1 Generating an XML Output File with No Options
      2. Example 8.16.2 Adjusting Column Width Using Tagset Template Options
      3. Example 8.16.3 Tagset Option to Hide Columns While Writing the File
      4. Example 8.16.4 Apply an Excel “AUTOFILTER” to Selected Output Columns
      5. Example 8.16.5 Using Multiple Options to Produce a “Ready-to-Print” Spreadsheet
      6. Example 8.16.6 Creating a Table of Contents in an Excel Workbook
      7. Example 8.16.7 Methods of Naming Excel Worksheets
      8. Example 8.16.8 Splitting One Report onto Multiple Excel Worksheets
      9. Example 8.16.9 Methods of Placing Labels in Excel Worksheet Names
      10. Example 8.16.10 Use SHEET_INTERVAL= BYGROUP to Create Worksheets
      11. Example 8.16.11 Use SHEET_INTERVAL= PROC to Create Worksheets
      12. Example 8.16.12 Build Separate Worksheets with Titles on Each Sheet
    17. 8.17 The New ODS Destination EXCEL for Writing Workbooks
    18. 8.18 Conclusion
  12. Chapter 9: Accessing Excel with OLE DB or ODBC Application Program Interfaces (API Methods)
    1. 9.1 Introduction
    2. 9.2 Purpose
    3. 9.3 Concept of the OLE DB or ODBC API Processes
    4. 9.4 Guidelines for Setting Up OLE DB or ODBC Connections
    5. 9.5 List of Examples
    6. 9.6 Examples
      1. Example 9.1 Assign a Libref to an Excel Worksheet with the OLE-DB Dialog Box
      2. Example 9.2 Using LIBNAME Prompt Mode to Build an OLE-DB Connection
      3. Example 9.3 Using an OLE-DB init_string to Open an Excel Workbook
      4. Example 9.4 Using PROC CONTENTS to Verify Excel to OLE DB Connection
    7. 9.7 Conclusion
  13. Chapter 10: Using PROC SQL to Access Excel Files
    1. 10.1 Introduction
    2. 10.2 Purpose
    3. 10.3 Basic Syntax of the SQL Procedure
    4. 10.4 A Simple Explanation of SQL “PASS-THROUGH” Processing
    5. 10.5 Overview of the Examples
    6. 10.5.1 List of Examples
    7. 10.6 Examples
      1. Example 10.1 LIBNAME Assignments to Access Excel Using PROC SQL
      2. Example 10.2 Create an Excel File, Read It with SQL, and Then Compare the Files
      3. Example 10.3 Use PROC SQL to Read a Subset of Records from an Excel Workbook
      4. Example 10.4 Use PROC SQL Pass-Through Facilities to Process an Excel File
      5. Example 10.5 Read a Pre-defined Range of Cells from an Excel Workbook
      6. Example 10.6 Calculate a New Variable within the SQL Code and Sort the Output
      7. Example 10.7 Examine the Contents and Structure of an Excel Workbook with a “PCFILES::” Special Query
    8. 10.7 Conclusion
  14. Chapter 11: Using DDE to Read and Write to Excel Workbooks
    1. 11.1 Introduction
    2. 11.2 Purpose
    3. 11.3 Basic Concept of the DDE Client-Server Environment
      1. 11.3.1 How the DDE Client-Server Relationship Works
      2. 11.3.2 General DDE Syntax and Options
    4. 11.4 List of User-Written SAS Macros That Can Enhance DDE Processing
      1. 11.4.1 SAS Macro to Start Excel
      2. 11.4.2 SAS Macro to SAS to Issue Commands to Excel
      3. 11.4.3 SAS Macro to Define a Range of Excel Cells for Processing
      4. 11.4.4 SAS Macro to Save the Contents of an Excel Workbook
      5. 11.4.5 SAS Macro to Close Excel Workbook
      6. 11.4.6 SAS Macro to Write All or Selected Variables to an Excel Output Workbook
    5. 11.5 List of Examples
    6. 11.6 Examples
      1. Example 11.6.1 The Hello World Project
      2. Example 11.6.2 The Hello World Project When the Excel Workbook Is Closed
      3. Example 11.6.3 The Hello World Project Using NOTAB and LRECL= Options
      4. Example 11.6.4 Writing “Hello World” to an Excel File Using DDE Macros
      5. Example 11.6.5 Writing a SAS Dataset to an Excel File Using the SAS_2_EXCEL DDE Macro
    7. 11.7 Conclusion
  15. Chapter 12: Building a System of Excel Macros Executable by SAS
    1. 12.1 Introduction
    2. 12.2 Purpose
    3. 12.3 General Design of a Tool to Control Excel Macros from SAS
      1. 12.3.1 Prepare a SAS File and Execute Excel to Process the Output
      2. 12.3.2 Prepare Excel to Open the File Output by SAS
      3. 12.3.3 Prepare Excel Macros to Reformat the Excel Workbooks
    4. 12.4 Automate the Tool So That SAS Creates a Formatted Excel Output Workbook
      1. 12.4.1 Eliminate the Manual Steps from the Processing
      2. 12.4.2 Create a SAS Output File with More Data and Control Information
      3. 12.4.3 Create an Excel Macro to Process the Output SAS File
      4. 12.4.4 Build an Excel Graph Using an Excel Macro
    5. 12.5 Conclusion
  16. Chapter 13: Building a System of Microsoft Windows Scripts to Control Excel Macros
    1. 13.1 Introduction
    2. 13.2 Purpose
    3. 13.3 Guidelines for Building and Using a VBS/VBA Macro Library
      1. 13.3.1 Create Naming Conventions for Storing and Executing VBS/VBA Macros
      2. 13.3.2 Set Up Workstation Options
      3. 13.3.3 Where to Store VBS/VBA Scripts and Macros
      4. 13.3.4 SAS Code to Execute a Visual Basic Script
      5. 13.3.5 Build a Parameter-Driven VBS Script to Control the Execution of Excel
      6. 13.3.6 Build a Control Macro for Each Excel Report
    4. 13.4 Conclusion
  17. Chapter 14: Create an Excel Workbook That Runs SAS Programs
    1. 14.1 Introduction
    2. 14.2 Purpose
    3. 14.3 Guidelines for Building an Excel User Form Interface
      1. 14.3.1 Common Excel and Excel User Form Terms
      2. 14.3.2 Introduction to the Integrated Development Environment (IDE)
      3. 14.3.3 Using the Integrated Development Environment (IDE) Toolbox Menu
      4. 14.3.4 Building a Sample Integrated Development Environment (IDE) Menu
      5. 14.3.5 Linking the Integrated Development Environment (IDE) Menu and the Data
      6. 14.3.6 Storing Control Information in the Excel Workbook Worksheets
      7. 14.3.7 Set Up Control Variables to Access Data Stored in the Workbook
      8. 14.3.8 Learn How to Make the Excel UserForm Execute
    4. 14.4 Excel VBA Routines to Make the Workbook UserForm Active
      1. 14.4.1 Initialize the User Form
      2. 14.4.2 Write the User Parameters to a File in a Working Directory
      3. 14.4.3 Copy Source Program from a Production Directory to the Working Directory
      4. 14.4.4 Verify the Output Batch File Points to the Correct SAS Run Time Module
      5. 14.4.5 A Routine to Save the Changes and Exit the Program
      6. 14.4.6 Directory Structure Associated with the Processing
      7. 14.4.7 Common Issues That Might Occur
      8. 14.4.8 Prepare a VBA Macro to Process Your Output Report
    5. 14.5 Conclusion
  18. Index

Product information

  • Title: Exchanging Data between SAS and Microsoft Excel
  • Author(s): William E. Benjamin
  • Release date: April 2015
  • Publisher(s): SAS Institute
  • ISBN: 9781629596907