Problem Note 43948: A filter string that contains the IN operator might fail after migration from the SAS® Add-In 2.1 for Microsoft Office
If you create a filter in the SAS Add-In 2.1 for Microsoft Office that uses the IN operator, and you typed the values into the field, the string of filter values might change after you migrate your spreadsheet to 4.2 or 4.3. This change to the filter means that no data is returned when you refresh. This problem only occurs when you manually type the values into the field and the values are character strings.
For example, say that you manually enter the following values using one of these two formats:
U.S.A., GERMANY
'U.S.A.', 'GERMANY'
In version 2.1, the filter string appears like following:
COUNTRY IN ('U.S.A.', 'GERMANY')
After you migrate your spreadsheet to, for example, 4.3, the filter is changed so that the entire string appears within quotation marks:
COUNTRY IN ('U.S.A., GERMANY')
COUNTRY IN ('''U.S.A.'',''GERMANY''')
When this change occurs, the filter can no longer find any matching values and no results are returned when you refresh.
To circumvent this issue, edit your filter and select the ellipse icon to remove the incorrect values, then select the correct values to rebuild the filter. The other option is to modify the values manually in the Advanced Filter Builder window.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| SAS System | SAS Add-in for Microsoft Office | Windows Vista for x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows Vista | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Ultimate x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Ultimate 32 bit | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Professional x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Professional 32 bit | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Home Premium x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Home Premium 32 bit | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Enterprise x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Windows 7 Enterprise 32 bit | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows XP Professional | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows Server 2008 for x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows Server 2008 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 for x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Standard Edition | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Enterprise Edition | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft Windows Server 2003 Datacenter Edition | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
| Microsoft® Windows® for x64 | 4.2 | 5.1 | 9.2 TS2M2 | 9.3 TS1M0 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2012-01-23 09:03:39 |
| Date Created: | 2011-08-09 13:41:51 |
