Using the SAS LIBNAME to Process Excel Files - Exchanging Data Between SAS and Microsoft Excel - page 56

4.9 Examples
Example 4.1 Using the Engine Connection HEADER Option
This example relates how the first row of Excel data is treated. If the Excel file contains descriptive names
of the data and is useful to be converted to a SAS variable name, then the default value of
“ HEADER=YES ” should be used. The SAS programmer can also code this option directly on the
LIBNAME statement. The output is shown in Figure 4.9.1.
LIBNAME xls_data EXCEL 'C:\My_excel_files\my_excel_file.xls' HEADER=NO;
DATA Excel_data;
Set xls_data.'sheet1'n;
Run;
Figure 4.9.1: Reading Excel Data with the HEADER=NO Option Turned On.
The HEADER=NO option suppresses using row one of the data in the Excel worksheet for the variable
names in the SAS output dataset. The SAS variable names generated are F1, F2, F3, F4, F5, F6, and F7 for
this file. The SAS dataset created is shown here. When we look at the first observation of the SAS dataset,
we expect to see the names of the variables as data values of observation number one. However, the first
row of the SAS dataset contains data values Region, Product, Subsidiary, “.”, “.”, “.”, and “.”. This occurs
because, when reading the data from Excel, an estimate is made about what type of data is in any given
column. Typically, the first eight rows of the Excel worksheet are scanned and the data is checked to see if
it has more character or numeric values. This testing determines whether or not the column is assigned to
use a character or numeric informat when the data is transferred to the SAS dataset. Therefore, the
character values in row 1, columns F4 to F7, of the Excel worksheet fail to be converted to numeric values
and are assigned a missing numeric value. This would be true for any character value in columns 4 through
7. Conversely, any numeric value in column 1 to 3 will be converted to a character missing value.