Database Reference
In-Depth Information
Chapter 1: Easy Data Movement between SAS and
Microsoft Excel
1.1 Introduction......................................................................................................1
1.2 Examination of Excel Files ...............................................................................2
1.2.1 Purpose ..........................................................................................................................2
1.2.2 Excel Data Types...........................................................................................................2
1.2.3 General Excel Workbook Limitations..........................................................................2
1.2.4 Excel Workbook Formatting Groups...........................................................................3
1.2.5 Excel Data Ranges ........................................................................................................4
1.3.1 Highlight, “Cut” or “Copy,” and Then “Paste” ...........................................................5
1.3.2 Convert Text Data to Excel Column Data Fields .......................................................5
1.3.4 Save Multiple Lines of Text in a Single Excel Cell .....................................................8
1.3.5 Converting Excel Tables to Text..................................................................................9
1.5 Chapter Summary.......................................................................................... 18
1.1 Introduction
It may seem a bit odd to start a book about SAS programming with examples of Excel files and their
limitations. But, because this information is at the front of the topic, it will be easy to locate when you want
to figure out why some of your data seems to be missing or how much data this Excel format can hold.
Increased computer memory and speed have spurred the growth of computer capabilities. For example, the
software known as either the Joint Engine Technology (JET) database engine or the Access Connectivity
Engine (ACE) are built into the Microsoft Windows operating systems. These Microsoft database access
engines are used to access data for several Microsoft products including Microsoft Excel. These Microsoft
database engines provide an interface to Excel (and other Microsoft products) that can be used by SAS and
other database interface tools to access data in Excel workbooks.
When you are looking at a computer monitor or a printed page, both a view of a SAS file and the display of
an Excel worksheet are very similar. Each image has an array of rows and columns with data values. SAS
calls them observations and variables while Excel calls them rows and columns. While most SAS users
change all the values of individual elements of a SAS dataset with a SAS program or procedure, the
