Well, I contacted SAS tech support and they had a solution which I will share for posterity.
It takes changing a couple of registry settings. Which ones depends on your OS, Version of Office, and version of SAS. For my Windows 7 64bit machine with Office 2007 and SAS 9.2, the TypeGuessRows value needed to be changed from 8 to 0.
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
Tech support sent me the following. Note, that on my machine, the Jet key was in a different location.
Or to make this simpler you can follow these instructions here.
1. You bring up the registry edit by typing REGEDIT from a start +> run windows prompt.
2. Expand HKEY_LOCAL_MACHINE by clicking on the plus + sign on the left
3. Expand SOFTWARE
4. Expand MICROSOFT
5. Expand JET
6. Expand 4.0
7. Expand Engines
8. Expand Excel
9. Double click on the TypeGuessRows on the right.
10. Change the value from 8 to 0.
Then exit and you are done.
If you are running 9.2 TS2M0 or later you should change this one or these keys as well depending
on the microsoft office you have installed .
To change the registry key for office 2007:
In Windows environments, select Start => Run and type REGEDIT to display the Registry Editor.
In the registry tree,
select HKEY_LOCAL_MACHINE => Software => Microsoft => Office => 12.0 => Access Connectivity Engine => Engines.
Double-click the Excel node.
In the right panel, double-click the TypeGuessRows entry.
Change the value data from 8 to 0.
Click OK.
Select File => Exit to exit the Registry Editor window.
If you are running 9.2 TS2M0 or later and have Office 2010 installed you should change this one instead.
To change the registry key:
In Windows environments, select Start => Run and type REGEDIT to display the Registry Editor.
In the registry tree,
select HKEY_LOCAL_MACHINE => Software => Microsoft => Office => 14.0 => Access Connectivity Engine => Engines.
Double-click the Excel node.
In the right panel, double-click the TypeGuessRows entry.
Change the value data from 8 to 0.
Click OK.
Select File => Exit to exit the Registry Editor window.
If you are running on a Windows 7 or X64 system you need to go a slight different registry key located here....
for office 2007 change the key below:
HKEY_LOCAL_MACHINE => Software => Wow6432Node => Microsoft => Office => 12.0 => Access Connectivity Engine => Engines
For Office 2010 change the key below:
HKEY_LOCAL_MACHINE => Software => Wow6432Node => Microsoft => Office => 14.0 => Access Connectivity Engine => Engines
Message was edited by: Curtis Mack