book

Exchanging Data between SAS and Microsoft Excel

Name: Exchanging Data between SAS and Microsoft Excel
Author: William E. Benjamin
ISBN: 9781629596907

by William E. Benjamin

April 2015

Beginner to intermediate

290 pages

8h 29m

English

SAS Institute

Read now

Unlock full access

Preface
About This Book
About The Author
Chapter 1: Easy Data Movement between SAS and Microsoft Excel
1.1 Introduction1.2 Examination of Excel Files1.2.1 Purpose1.2.2 Excel Data Types1.2.3 General Excel Workbook Limitations1.2.4 Excel Workbook Formatting Groups1.2.5 Excel Data Ranges1.3 Examples of Copy-and-Paste Techniques1.3.1 Highlight, “Cut” or “Copy,” and Then “Paste”1.3.2 Convert Text Data to Excel Column Data Fields1.3.3 Copy Data to the SAS Enhanced Editor Window for Use in a SAS Program1.3.4 Save Multiple Lines of Text in a Single Excel Cell1.3.5 Converting Excel Tables to Text1.4 Accessing Excel Data from the SAS Explorer Window and Toolbar1.4.1 SAS Explorer Window and Toolbar Processing Method Descriptions1.4.2 Picking the Export Wizard from the SAS Explorer Window “Export” Menu1.4.3 Using the “Copy Contents to Clipboard” Option of the SAS Explorer Window1.4.4 Selecting the “Save as Html” Option of the SAS Explorer Window1.4.5 Using the “View in Excel” Option to Copy Data to Excel Files via HTML1.4.6 SAS Toolbar File Option, the Gateway to the SAS Export / Import Wizards1.4.7 Choosing the “Export Data” SAS Toolbar (Export Wizard) File Option1.4.8 Electing the “Import Data” SAS Toolbar (Import Wizard) File Option1.4.9 Using the Export / Import Wizards in a 32/64-Bit Mixed Environment1.5 Chapter Summary
Chapter 2: Use PROC EXPORT to Write SAS Data to External Files and Excel Workbooks
2.1 Introduction2.2 Purpose2.3 Syntax of the SAS EXPORT Procedure2.4 Data Access Methods for Excel Files Supported by PROC EXPORT2.5 Overview of the Examples2.6 List of ExamplesExample 2.1 SAS Code to Export Data to an Excel 4 or Excel 5 Format FileExample 2.2 PROC EXPORT Using the DBMS=DLM OptionExample 2.3 PROC EXPORT Using the DBMS=EXCEL OptionExample 2.4 PROC EXPORT Using the DBMS=EXCELCS OptionExample 2.5 SAS Code to Export Data to an Excel File with No Column HeadersExample 2.6 SAS Code to Export Data to a Network Windows Computer2.7 Conclusion
Chapter 3: Use PROC IMPORT to Read External Data Files and Excel Workbooks into SAS
3.1 Introduction3.2 Purpose3.3 Syntax of the SAS IMPORT Procedure3.4 Data Access Methods for Files Supported by PROC IMPORT3.5 Overview of the Examples3.6 List of ExamplesExample 3.1 PROC IMPORT Using the DBMS=EXCEL4 or EXCEL5 OptionExample 3.2 PROC IMPORT Using the DBMS=DLM OptionExample 3.3 PROC IMPORT Using the DBMS=EXCEL OptionExample 3.4 PROC IMPORT Using the DBMS=EXCELCS OptionExample 3.5 PROC IMPORT Using the DBMS=XLS or XLSX to Select ColumnsExample 3.6 PROC IMPORT Using the DBMS=XLS or XLSX to Select RowsExample 3.7 PROC IMPORT Using the DBMS=XLS or XLSX to Select Excel Ranges3.7 Conclusion
Chapter 4: Using the SAS LIBNAME to Process Excel Files
4.1 Introduction4.2 Purpose4.3 Excel-Specific Features of the SAS LIBNAME Statement4.4 Syntax of the SAS LIBNAME Statement4.5 LIBNAME Statement ENGINE CONNECTION OPTION Descriptions4.5.1 HEADER Option to Read Variable Names4.5.2 MIXED Option to Select Data Types4.5.3 PATH Option to Define Physical File Locations4.5.4 VERSION Option to Identify Excel File Version4.5.5 PROMPT Option to Interactively Assign a Libref4.5.6 Other Common SAS PC File LIBNAME Options4.6 Excel-Specific Dataset Options4.7 UNIX, LINUX, and 64-Bit Windows Connection Options4.8 Overview of the Examples4.8 List of Examples4.9 ExamplesExample 4.1 Using the Engine Connection HEADER OptionExample 4.2 Using the Engine Connection MIXED OptionExample 4.3 Using the Engine Connection PATH OptionExample 4.4 Using the Engine Connection VERSION OptionExample 4.5 Using Named Literals with the LIBNAME StatementExample 4.6 Using PROC CONTENTS to Examine an Excel WorkbookExample 4.7 Using Dataset Options to Process Date and Time ValuesExample 4.8 Using Dataset Options to Process Variable Type ConversionsExample 4.9 Processing on 64-Bit Operating Systems4.10 Conclusion
Chapter 5: SAS Enterprise Guide Methods and Examples
5.1 Introduction5.2 Purpose5.3 Typical Methods to Access Excel from SAS Enterprise Guide5.4 Overview of the Examples5.5 List of Examples5.6 ExamplesExample 5.1 Using the Export Method with Enterprise GuideExample 5.2 Using the “Send To” MethodExample 5.3 Using the “Send To” Method to Output a Graph or ReportExample 5.4 Using the “Export” Method to Output a Graph or ReportExample 5.5 Using “Open” or “Import” Toolbar Options to Read Excel WorkbooksExample 5.6 Using the “Import Data” Toolbar Option to Read a Range of Cells5.7 Conclusion
Chapter 6: Using JMP to Share Data with Excel
6.1 Introduction6.2 Purpose6.3 Methods of Sharing Data between JMP and Excel6.4 List of Examples6.5 ExamplesExample 6.1 Within Excel, Set the JMP Preferences for Loading Excel DataExample 6.2 Reading Data from Excel to JMPExample 6.3 Writing Data from JMP to Excel6.6 Conclusion
Chapter 7: SAS Add-In for Microsoft Office (Excel)
7.1 Introduction7.2 Purpose7.3 Methods of Sharing Data Using SAS Add-In for Microsoft Office7.4 List of Examples7.5 ExamplesExample 7.1 Open a SAS Dataset Using SAS Add-In for Microsoft OfficeExample 7.2 Open a SAS Report Dataset (*.srx) Using SAS Add-In for Microsoft Office7.6 Conclusion

Chapter 8: Creating Output Files with ODS for Use by Excel
8.1 Introduction8.2 Purpose8.3 An Introduction to SAS Tagset Templates That Create Files for Excel8.3.1 How to Locate a Tagset Template8.4 Difference Between an ODS Tagset and an ODS Destination8.5 Syntax of the ODS CSV and CSVALL Output Processes8.6 CSV and CSVALL Tagset Options8.7 Overview of CSV and CSVALL Examples8.8 CSV and CSVALL Examples to Write *.csv FilesExample 8.8.1 Simple CSV and CSVALL File Default Output DifferencesExample 8.8.2 CSV and CSVALL Title and Footnote Output DifferencesExample 8.8.3 Write Currency Values as Unformatted NumbersExample 8.8.4 Change Delimiters When Outputting Data with CSV TagsetExample 8.8.5 Save Leading Zeroes in Character Fields Sent to Excel8.9 Syntax of ODS MSOFFICE2K Output Processes to Write HTML Files8.10 MSOFFICE2K Tagset Template Options8.11 Overview of MSOFFICE2K Examples8.12 MSOFFICE2K Examples to Write HTML FilesExample 8.12.1 Generating an HTML Output File with No OptionsExample 8.12.2 Generating an HTML File Using the Summary_Vars Option8.13 Syntax of the ODS EXCELXP Tagset Template Output Processes8.14 ODS EXCELXP Tagset Options8.15 Overview of EXCELXP Examples8.16 EXCELXP Examples to Write XML FilesExample 8.16.1 Generating an XML Output File with No OptionsExample 8.16.2 Adjusting Column Width Using Tagset Template OptionsExample 8.16.3 Tagset Option to Hide Columns While Writing the FileExample 8.16.4 Apply an Excel “AUTOFILTER” to Selected Output ColumnsExample 8.16.5 Using Multiple Options to Produce a “Ready-to-Print” SpreadsheetExample 8.16.6 Creating a Table of Contents in an Excel WorkbookExample 8.16.7 Methods of Naming Excel WorksheetsExample 8.16.8 Splitting One Report onto Multiple Excel WorksheetsExample 8.16.9 Methods of Placing Labels in Excel Worksheet NamesExample 8.16.10 Use SHEET_INTERVAL= BYGROUP to Create WorksheetsExample 8.16.11 Use SHEET_INTERVAL= PROC to Create WorksheetsExample 8.16.12 Build Separate Worksheets with Titles on Each Sheet8.17 The New ODS Destination EXCEL for Writing Workbooks8.18 Conclusion
Chapter 9: Accessing Excel with OLE DB or ODBC Application Program Interfaces (API Methods)
9.1 Introduction9.2 Purpose9.3 Concept of the OLE DB or ODBC API Processes9.4 Guidelines for Setting Up OLE DB or ODBC Connections9.5 List of Examples9.6 ExamplesExample 9.1 Assign a Libref to an Excel Worksheet with the OLE-DB Dialog BoxExample 9.2 Using LIBNAME Prompt Mode to Build an OLE-DB ConnectionExample 9.3 Using an OLE-DB init_string to Open an Excel WorkbookExample 9.4 Using PROC CONTENTS to Verify Excel to OLE DB Connection9.7 Conclusion
Chapter 10: Using PROC SQL to Access Excel Files
10.1 Introduction10.2 Purpose10.3 Basic Syntax of the SQL Procedure10.4 A Simple Explanation of SQL “PASS-THROUGH” Processing10.5 Overview of the Examples10.5.1 List of Examples10.6 ExamplesExample 10.1 LIBNAME Assignments to Access Excel Using PROC SQLExample 10.2 Create an Excel File, Read It with SQL, and Then Compare the FilesExample 10.3 Use PROC SQL to Read a Subset of Records from an Excel WorkbookExample 10.4 Use PROC SQL Pass-Through Facilities to Process an Excel FileExample 10.5 Read a Pre-defined Range of Cells from an Excel WorkbookExample 10.6 Calculate a New Variable within the SQL Code and Sort the OutputExample 10.7 Examine the Contents and Structure of an Excel Workbook with a “PCFILES::” Special Query10.7 Conclusion
Chapter 11: Using DDE to Read and Write to Excel Workbooks
11.1 Introduction11.2 Purpose11.3 Basic Concept of the DDE Client-Server Environment11.3.1 How the DDE Client-Server Relationship Works11.3.2 General DDE Syntax and Options11.4 List of User-Written SAS Macros That Can Enhance DDE Processing11.4.1 SAS Macro to Start Excel11.4.2 SAS Macro to SAS to Issue Commands to Excel11.4.3 SAS Macro to Define a Range of Excel Cells for Processing11.4.4 SAS Macro to Save the Contents of an Excel Workbook11.4.5 SAS Macro to Close Excel Workbook11.4.6 SAS Macro to Write All or Selected Variables to an Excel Output Workbook11.5 List of Examples11.6 ExamplesExample 11.6.1 The Hello World ProjectExample 11.6.2 The Hello World Project When the Excel Workbook Is ClosedExample 11.6.3 The Hello World Project Using NOTAB and LRECL= OptionsExample 11.6.4 Writing “Hello World” to an Excel File Using DDE MacrosExample 11.6.5 Writing a SAS Dataset to an Excel File Using the SAS_2_EXCEL DDE Macro11.7 Conclusion
Chapter 12: Building a System of Excel Macros Executable by SAS
12.1 Introduction12.2 Purpose12.3 General Design of a Tool to Control Excel Macros from SAS12.3.1 Prepare a SAS File and Execute Excel to Process the Output12.3.2 Prepare Excel to Open the File Output by SAS12.3.3 Prepare Excel Macros to Reformat the Excel Workbooks12.4 Automate the Tool So That SAS Creates a Formatted Excel Output Workbook12.4.1 Eliminate the Manual Steps from the Processing12.4.2 Create a SAS Output File with More Data and Control Information12.4.3 Create an Excel Macro to Process the Output SAS File12.4.4 Build an Excel Graph Using an Excel Macro12.5 Conclusion
Chapter 13: Building a System of Microsoft Windows Scripts to Control Excel Macros
13.1 Introduction13.2 Purpose13.3 Guidelines for Building and Using a VBS/VBA Macro Library13.3.1 Create Naming Conventions for Storing and Executing VBS/VBA Macros13.3.2 Set Up Workstation Options13.3.3 Where to Store VBS/VBA Scripts and Macros13.3.4 SAS Code to Execute a Visual Basic Script13.3.5 Build a Parameter-Driven VBS Script to Control the Execution of Excel13.3.6 Build a Control Macro for Each Excel Report13.4 Conclusion
Chapter 14: Create an Excel Workbook That Runs SAS Programs
14.1 Introduction14.2 Purpose14.3 Guidelines for Building an Excel User Form Interface14.3.1 Common Excel and Excel User Form Terms14.3.2 Introduction to the Integrated Development Environment (IDE)14.3.3 Using the Integrated Development Environment (IDE) Toolbox Menu14.3.4 Building a Sample Integrated Development Environment (IDE) Menu14.3.5 Linking the Integrated Development Environment (IDE) Menu and the Data14.3.6 Storing Control Information in the Excel Workbook Worksheets14.3.7 Set Up Control Variables to Access Data Stored in the Workbook14.3.8 Learn How to Make the Excel UserForm Execute14.4 Excel VBA Routines to Make the Workbook UserForm Active14.4.1 Initialize the User Form14.4.2 Write the User Parameters to a File in a Working Directory14.4.3 Copy Source Program from a Production Directory to the Working Directory14.4.4 Verify the Output Batch File Points to the Correct SAS Run Time Module14.4.5 A Routine to Save the Changes and Exit the Program14.4.6 Directory Structure Associated with the Processing14.4.7 Common Issues That Might Occur14.4.8 Prepare a VBA Macro to Process Your Output Report14.5 Conclusion
Index

Content preview from Exchanging Data between SAS and Microsoft Excel

Preface

Over time, I have learned that, to keep life simple, you give the boss data in the format he or she wants. In other words, if the boss wants an Excel workbook, you deliver an Excel workbook with all the data and formatting requested. If the data comes from Excel, you need to find a way to read the data from the Excel files. For this book, I wrestled with the question of how to describe merging the SAS and Excel distinct skill sets in a way that would be understandable to both groups. This book is primarily written for people who need to transfer data between SAS and Excel. That usually means that you are well-versed in one application, but not the other. My choice of words was very careful when I used the word “BETWEEN.”

I have included ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781607649854

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Exchanging Data between SAS and Microsoft Excel

by William E. Benjamin

Preface

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.