1# warrenzhang
首先感谢楼上2位的热心帮助！！！
终于找到问题的根源了，原来是微软jet engine的设置问题。
参见SAS帮助:
MIXED=YES | NO; (available only for DBMS=EXCEL) specifies whether to convert numeric data into character data values for a column that contains mixed data types. The Microsoft Jet/Excel engine handles this option.
| YES | assigns a SAS character type for the column and converts all numeric data values to character data values when mixed data types are found. When you specify MIXED=YES, the connection is set in import mode and no updates are allowed.
CAUTION:Due to a limitation in the Microsoft Jet/Excel engine, using the MIXED=YES option might result in improper text variable lengths. It is strongly recommended that you first follow the steps below and check that you are receiving the results that you expect before you continue.  From Microsoft Windows, follow these steps to change the value of TypeGuessRows.
- Start
 Run In the Run window dialog box, enter Regedt32 and select OK. - Open the following key in the Registry Editor window:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel - Double-click TypeGuessRows.
- In the Edit DWORD dialog box under Base, select Decimal, enter 0 in the Value field, and select OK.
- Exit from the Registry Editor window.
|
| NO | assigns numeric or character type for the column, depending on the majority of the type data that is found. Numeric data in a character column is imported as missing values, and character data in a numeric column is imported as missing values.
|
The following registry settings can affect the behavior of the MIXED= option. They are located in the [\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] key of the Microsoft Windows registry.
Registry Settings for the MIXED OptionTypeGuessRowsan integer type with a default value of 8. You can use the number of rows in the worksheet range in scans to determine column types. If you set this type to 0, all rows in the range are checked. Microsoft states that the valid range of TypeGuessRows is 0-16. However, you could set as high as 16384, and it would still operate correctly.CAUTION:Not only can changing the TypeGuessRows value cause a scan to fail if you set it higher than 16384, but it affects any software that uses the Microsoft Jet provider to access Excel file data, including accessing Excel data in a Microsoft Access database. The TypeGuessRows value is registered with and controlled by Microsoft. It is therefore recommended that you set the value to 0.
ImportMixedTypesa string type with a default value of Text. If a column contains more than one type of data while scanning TypeGuessRows rows, the type of the column is determined to be Text if the value of the setting is Text. If the value of the setting is Majority, the most common type in the column determines the column type. For the MIXED=YES option to work correctly, you should you change TypeGuessingRows to 0 in the Microsoft Windows registry so that all the rows in the specified range are scanned. As a result, when you use MIXED=YES, the Jet provider always assigns character type for columns with data of mixed data types and converts numeric data to character data.
也就是说，默认状态MS Jet是把混合型数据当成字符串（text）处理的，因此如果import的选项mixed=yes打开的话，就会有可能出现上述结果。根据提示，应该把注册表项TypeGuessingRows改0，ImportMixedTypes改Majority。这样做才能从根本上解决问题。
PS。强烈鄙视微软！！！鄙视EXCEL！！！遗憾的是，许多公司都在把excel当数据库用，简直是莫名其妙。用csv格式多好啊，xls记录2维表格非常臃肿，数据处理也不方便。
发帖
雷达卡
京公网安备 11010802022788号
