When you use the SQL procedure and the following conditions apply to your query, an invalid query might be passed to the database:
- You access a database using a LIBNAME engine.
- You use a set operator including UNION, EXCEPT, INTERSECT, and OUTER UNION.
- A variable is renamed in both table expressions.
- A new constant column is created in both table expressions.
- A WHERE clause is the second table expression.
This sample program provides an example of code that generates the error:
proc sql;
create table t1 as
select name as who, 'class1' as newvar
from mydb.class1
union
select name as who, 'class2' as newvar
from mydb.class2
where age=14;
quit;
The invalid query results in a database error. So SAS retrieves the data and does the processing instead. This issue likely results in slower performance.
Here are the workarounds:
- Do not rename the column in the second SELECT statement.
- Use explicit pass-through to pass the query to the database.