eBook free download

Book Details

• Paperback: 290 pages
• Publisher: SAS Institute (March 25, 2015)
• Language: English
• ISBN-10: 1607649853
• ISBN-13: 978-1607649854
  

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.

Contents
Preface
About This Book
About The Author
Chapter 1: Easy Data Movement between SAS and Microsoft Excel.
1.1 Introduction
1.2 Examination of Excel Files
1.2.1 Purpose
1.2.2 Excel Data Types
1.2.3 General Excel Workbook Limitations
1.2.4 Excel Workbook Formatting Groups
1.2.5 Excel Data Ranges
1.3 Examples of Copy-and-Paste Techniques
1.3.1 Highlight, “Cut” or “Copy,” and Then “Paste”
1.3.2 Convert Text Data to Excel Column Data Fields
1.3.3 Copy Data to the SAS Enhanced Editor Window for Use in a SAS Program
1.3.4 Save Multiple Lines of Text in a Single Excel Cell
1.3.5 Converting Excel Tables to Text
1.4 Accessing Excel Data from the SAS Explorer Window and Toolbar
1.4.1 SAS Explorer Window and Toolbar Processing Method Descriptions
1.4.2 Picking the Export Wizard from the SAS Explorer Window “Export” Menu
1.4.3 Using the “Copy Contents to Clipboard” Option of the SAS Explorer Window
1.4.4 Selecting the “Save as Html” Option of the SAS Explorer Window
1.4.5 Using the “View in Excel” Option to Copy Data to Excel Files via HTML
1.4.6 SAS Toolbar File Option, the Gateway to the SAS Export / Import Wizards
1.4.7 Choosing the “Export Data” SAS Toolbar (Export Wizard) File Option
1.4.8 Electing the “Import Data” SAS Toolbar (Import Wizard) File Option
1.4.9 Using the Export / Import Wizards in a 32/64-Bit Mixed Environment
1.5 Chapter Summary
Chapter 2: Use PROC EXPORT to Write SAS Data to External Files and Excel
Workbooks
2.1 Introduction
2.2 Purpose
2.3 Syntax of the SAS EXPORT Procedure
2.4 Data Access Methods for Excel Files Supported by PROC EXPORT.
2.5 Overview of the Examples
From Exchanging Data Between SAS® and Microsoft
Excel. Full book available for purchase here.
iv
2.6 List of Examples
Example 2.1 SAS Code to Export Data to an Excel 4 or Excel 5 Format File
Example 2.2 PROC EXPORT Using the DBMS=DLM Option
Example 2.3 PROC EXPORT Using the DBMS=EXCEL Option
Example 2.4 PROC EXPORT Using the DBMS=EXCELCS Option
Example 2.5 SAS Code to Export Data to an Excel File with No Column Headers
Example 2.6 SAS Code to Export Data to a Network Windows Computer
2.7 Conclusion
Chapter 3: Use PROC IMPORT to Read External Data Files and Excel Workbooks
into SAS
3.1 Introduction
3.2 Purpose
3.3 Syntax of the SAS IMPORT Procedure
3.4 Data Access Methods for Excel Files Supported by PROC IMPORT
3.5 Overview of the Examples
3.6 List of Examples
Example 3.1 PROC IMPORT Using the DBMS=EXCEL4 or EXCEL5 Option
Example 3.2 PROC IMPORT Using the DBMS=DLM Option
Example 3.3 PROC IMPORT Using the DBMS=EXCEL Option
Example 3.4 PROC IMPORT Using the DBMS=EXCELCS Option
Example 3.5 PROC IMPORT Using the DBMS=XLS or XLSX to Select Columns
Example 3.6 PROC IMPORT Using the DBMS=XLS or XLSX to Select Rows
Example 3.7 PROC IMPORT Using the DBMS=XLS or XLSX to Select Excel Ranges
3.7 Conclusion
Chapter 4: Using the SAS LIBNAME to Process Excel Files
4.1 Introduction
4.2 Purpose
4.3 Excel-Specific Features of the SAS LIBNAME Statement
4.4 Syntax of the SAS LIBNAME Statement
4.5 LIBNAME Statement ENGINE CONNECTION OPTION Descriptions
4.5.1 HEADER Option to Read Variable Names
4.5.2 MIXED Option to Select Data Types
4.5.3 PATH Option to Define Physical File Locations
4.5.4 VERSION Option to Identify Excel File Version
4.5.5 PROMPT Option to Interactively Assign a Libref
4.5.6 Other Common SAS PC File LIBNAME Options
4.6 Excel-Specific Dataset Options
4.7 UNIX, LINUX, and 64-Bit Windows Connection Options
4.8 Overview of the Examples
4.8 List of Examples
4.9 Examples
Example 4.1 Using the Engine Connection HEADER Option
Example 4.2 Using the Engine Connection MIXED Option
Example 4.3 Using the Engine Connection PATH Option
v
Example 4.4 Using the Engine Connection VERSION Option
Example 4.5 Using Named Literals with the LIBNAME Statement
Example 4.6 Using PROC CONTENTS to Examine an Excel Workbook
Example 4.7 Using Dataset Options to Process Date and Time Values
Example 4.8 Using Dataset Options to Process Variable Type Conversions
Example 4.9 Processing on 64-Bit Operating Systems
4.10 Conclusion
Chapter 5: SAS Enterprise Guide Methods and Examples
5.1 Introduction
5.2 Purpose
5.3 Typical Methods to Access Excel from SAS Enterprise Guide
5.4 Overview of the Examples
5.5 List of Examples
5.6 Examples.
Example 5.1 Using the Export Method with Enterprise Guide
Example 5.2 Using the “Send To” Method
Example 5.3 Using the “Send To” Method to Output a Graph or Report
Example 5.4 Using the “Export” Method to Output a Graph or Report
Example 5.5 Using “Open” or “Import” Toolbar Options to Read Excel Workbooks
Example 5.6 Using the “Import Data” Toolbar Option to Read a Range of Cells
5.7 Conclusion
Chapter 6: Using JMP to Share Data with Excel
6.1 Introduction
6.2 Purpose
6.3 Methods of Sharing Data between JMP and Excel
6.4 List of Examples
6.5 Examples
Example 6.1 Within Excel, Set the JMP Preferences for Loading Excel Data
Example 6.2 Reading Data from Excel to JMP
Example 6.3 Writing Data from JMP to Excel
6.6 Conclusion
Chapter 7: SAS Add-In for Microsoft Office (Excel)
7.1 Introduction
7.2 Purpose
7.3 Methods of Sharing Data Using SAS Add-In for Microsoft Office
7.4 List of Examples
7.5 Examples
Example 7.1 Open a SAS Dataset Using SAS Add-In for Microsoft Office
Example 7.2 Open a SAS Report Dataset (*.srx) Using SAS Add-In for Microsoft Office
7.6 Conclusion
vi
Chapter 8: Creating Output Files with ODS for Use by Excel
8.1 Introduction
8.2 Purpose
8.3 An Introduction to SAS Tagset Templates That Create Files for Excel
8.3.1 How to Locate a Tagset Template
8.4 Difference Between an ODS Tagset and an ODS Destination
8.5 Syntax of the ODS CSV and CSVALL Output Processes
8.6 CSV and CSVALL Tagset Options
8.7 Overview of CSV and CSVALL Examples
8.8 CSV and CSVALL Examples to Write *.csv Files
Example 8.8.1 Simple CSV and CSVALL File Default Output Differences
Example 8.8.2 CSV and CSVALL Title and Footnote Output Differences
Example 8.8.3 Write Currency Values as Unformatted Numbers
Example 8.8.4 Change Delimiters When Outputting Data with CSV Tagset
Example 8.8.5 Save Leading Zeroes in Character Fields Sent to Excel
8.9 Syntax of ODS MSOFFICE2K Output Processes to Write HTML Files
8.10 MSOFFICE2K Tagset Template Options
8.11 Overview of MSOFFICE2K Examples
8.12 MSOFFICE2K Examples to Write HTML Files
Example 8.12.1 Generating an HTML Output File with No Options
Example 8.12.2 Generating an HTML File Using the Summary_Vars Option
8.13 Syntax of the ODS EXCELXP Tagset Template Output Processes
8.14 ODS EXCELXP Tagset Options
8.15 Overview of EXCELXP Examples
8.16 EXCELXP Examples to Write XML Files
Example 8.16.1 Generating an XML Output File with No Options
Example 8.16.2 Adjusting Column Width Using Tagset Template Options
Example 8.16.3 Tagset Option to Hide Columns While Writing the File
Example 8.16.4 Apply an Excel “AUTOFILTER” to Selected Output Columns
Example 8.16.5 Using Multiple Options to Produce a “Ready-to-Print” Spreadsheet
Example 8.16.6 Creating a Table of Contents in an Excel Workbook
Example 8.16.7 Methods of Naming Excel Worksheets
Example 8.16.8 Splitting One Report onto Multiple Excel Worksheets
Example 8.16.9 Methods of Placing Labels in Excel Worksheet Names
Example 8.16.10 Use SHEET_INTERVAL= BYGROUP to Create Worksheets
Example 8.16.11 Use SHEET_INTERVAL= PROC to Create Worksheets
Example 8.16.12 Build Separate Worksheets with Titles on Each Sheet
8.17 The New ODS Destination EXCEL for Writing Workbooks
8.18 Conclusion~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chapter 9: Accessing Excel with OLE DB or ODBC Application Program
Chapter 10: Using PROC SQL to Access Excel Files
Chapter 11: Using DDE to Read and Write to Excel Workbooks
Chapter 12: Building a System of Excel Macros Executable by SAS
Chapter 13: Building a System of Microsoft Windows Scripts to Control Excel
Chapter 14: Create an Excel Workbook That Runs SAS Programs