Database Reference
In-Depth Information
Excel spreadsheet user often modifies one element (or cell) at a time. The major difference between SAS
datasets and Excel worksheets is that even though a SAS user can open a SAS dataset in “edit” mode and
change the values of individual elements, SAS will enforce data type restrictions. A character value cannot
be placed into a variable defined as a numeric variable. A number can be placed into a character variable,
but it will be converted to a character value. Excel has no such restriction. This puts a burden of examining
the output data on the user. This examination is often skipped and data losses can occur.
1.2 Examination of Excel Files
1.2.1 Purpose
Let's take a minute to look at why we need to know anything about Excel files. Excel files have changed
over time. In the beginning, they had one worksheet and very few features or capabilities and proprietary
data formats. Today, the features, options, and capabilities of Excel are impressive. We will start here with
the simple things and, by the end of the topic, show you not only how SAS can send data to Excel, but also
how Excel can interact with SAS.
1.2.2 Excel Data Types
This section provides general descriptions of Excel Workbook sheet elements with row and column
limitations, along with general descriptions of the file formats, data types, and range definitions.
Formulas
Excel cells can contain formulas. While these are not actually data types, the action taken by a formula may
represent and be displayed as either a number or a character value. The SAS EXCELXP tagset can process
character strings and output them to Excel as a formula within an Excel workbook.
Character
Character data fields can contain any text values and are usually alphanumeric data strings that may contain
characters, words, numbers, Excel formulas, date and/or time values including special characters. Up to
32,767 (32KB-1) characters may be stored in one cell.
Numeric
Numeric values are usually numbers like the digits 0-9, but the data in these cells may also include
formulas. The numeric character type also includes several special values that indicate error conditions like
#NULL!, #N/A, #VALUE!. Numeric dates are represented as numbers the integer portion is the number of
days and the fractional portion is the number of minutes and seconds within 24 hours. The counter resets at
midnight to 0.0, noon is 0.5, and one second before midnight is represented as 0.999988.
Other Excel Data Display Formats
Excel, like SAS, can display numeric data in several different formats. These include displays such as a
currency, percentage, accounting, numbers with integer and fractional parts, several date formats, and
several numeric formats. Other display formats exist but the data values underlying the display are either
character or numeric.
Other Excel File Elements
In addition to the data elements of an Excel file, an Excel workbook can also contain charts, graphs, and
other images. The SAS Enterprise Guide features described in this topic can generate SAS graphs that can
be sent to Excel and displayed as Excel worksheet elements.
1.2.3 General Excel Workbook Limitations
Table 1.2.1 describes Excel workbook limitations and characteristics for versions of Microsoft Excel
formats. As noted in the table, the size and capacity of Excel workbook files has grown over the years.
However, care must be taken when processing large Excel files because the Microsoft JET and ACE
Search WWH ::
Custom Search