SUPPORT / SAMPLES & SAS NOTES
Problem Note 19178: Migrating projects might cause incorrect Query results in SAS® Enterprise Guide®
 |  |  |  |  |
WHERE clause syntax in a query task might be altered during project migration possibly causing incorrect results in SAS Enterprise Guide. This will most likely occur when migrating to SAS Enterprise Guide 4.1 and for relatively complex WHERE clause syntax.
For example, suppose a query contained a WHERE clause similar to this one using the table SASHELP.CLASS in SAS Enterprise Guide 3.x:
WHERE ( CLASS.Name NOT = 'test' AND ( ( CLASS.Name IN ('Alfred',
'Alice') AND ( CLASS.Sex = 'F' OR ( CLASS.Sex = 'M' AND
CLASS.Sex = 'A' ) )) OR CLASS.Name NOT IN ('Alfred', 'Alice') ) );
If the project was migrated to SAS Enterprise Guide 4.1.x, the WHERE clause might be altered to this:
WHERE CLASS.Name <> "test" AND ( CLASS.Name IN ("Alfred", "Alice")
OR CLASS.Name NOT IN ("Alfred", "Alice") AND ( CLASS.Sex = "F" OR
( CLASS.Sex= "M" AND CLASS.Sex = "A" ) ) );
The above clause returns completely different results.
The only circumvention is to manually modify the resulting WHERE clause using steps similar to these:
- Open the combine filters dialog.
- In the second row, ctrl-select the first filter [CLASS.Name IN ("Alfred", "Alice")] in the first row and the last grouping icon in the row. This should be the grouping icon represented by ( ).
- Click the Group button to group the two selected items.
- In the newly formed third row, change the OR to AND, press OK and run the query.
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Enterprise Guide | Microsoft Windows XP Professional | 4.1 | 4.2 | 9.1 TS1M3 SP1 | 9.2 TS2M0 |