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

NOTE: There is a way to change the system-wide default for the GUESSINGROWS option, but it involves
modifying the SAS or WINDOWS system REGISTRY values.
Example 4.2 Using the Engine Connection MIXED Option
This example uses the MIXED=YES engine connection option to capture all data in the Excel worksheet
as character data values. This eliminates missing values in fields with mixed character and numeric values
in individual Excel cells. But it also places the burden of converting numeric data on the program that uses
the SAS dataset that was created. The code in Figure 4.9.2 will run using 32-bit SAS V9.2 on a 32-bit
operating system or 32-bit SAS V9.3 on a 32-bit operating system.
LIBNAME xls_data EXCEL 'C:\My_excel_files\my_excel_file.xls'
HEADER=NO MIXED=YES;
DATA Excel_data;
SET xls_data.'sheet1'n;
RUN;
Figure 4.9.2: Reading Excel Data with the MIXED=YES and the HEADER=NO Options
Turned On.
The data shown in Figure 4.9.2 is an example of the MIXED option with a value of YES, combined with
HEADER=NO option. The combined use of these two options changed the values in row 1 from missing
values to the text from the worksheet for columns F4 through F7. The HEADER=NO option changes only
the top row of the SAS file. In addition, note that the MIXED=YES option affected all of the data in all of
the columns by left justifying it. That means that all of the data in the worksheet is read as character data.
This trick works only if the first row has character headers in every column.
As an aside, and not shown here, notice that the SAS variable names are all in a pattern: “F1”, “F2”, etc.
An alternative to hand-coding a rename for all of the variables is to read only one row from the file and
process the SAS file of headers with PROC CONTENTS and SQL to create a macro variable that can be
used to create a rename command.