An SQL procedure query might result in a SQLPLAN error when you attempt to subset a view based on a variable that has previously been defined with a column modifier. 

The error might look similar to the following:

This issue has been identified in the following three scenarios. However, there are likely more scenarios where this issue might occur.

Scenario 1: 

1. You create an SQL view of one table.
2. You create a second SQL view where the following is true:
   • The first view and a table are joined using an INNER JOIN with an ON clause or a NATURAL INNER JOIN.
   • At least one of the variables used in the join is defined with a modifier including LENGTH=, LABEL=, INFORMAT=, and FORMAT=.
3. You create a third SQL view where the following is true:
   • The second view and a table are joined using an INNER JOIN with an ON clause or a NATURAL INNER JOIN. The join type can be the same or different from what was used in the second view.
   • The same variables are defined with a column modifier. The modifier can be the same or a different modifier from what was used in the second view.
4. You create a forth SQL view where the following is true:
   • The query references the third view.
   • The same variables are defined with a column modifier. The modifier can be the same or a different modifier from what was used in the second and third view.
5. Your SQL query subsets the forth view with a WHERE clause using a variable that was defined with a column modifier.

Scenario 2:

1. You create an SQL view of one table.
2. You create a second SQL view where the following is true:
   • The first view and a table are joined using an INNER JOIN with an ON clause, a NATURAL INNER JOIN, or by coding the INNER JOIN using a comma between the tables rather than using INNER JOIN syntax.
   • At least one of the variables used in the join is defined with a modifier including LENGTH=, LABEL=, INFORMAT=, and FORMAT=.
3. You create a third SQL view where the following is true:
   • The second view and a table are joined using an INNER, LEFT, CROSS, NATURAL INNER, or NATURAL LEFT JOIN with an ON clause or a NATURAL INNER JOIN. The join type can be the same or different from what was used in the second view.
   • The same variables are defined with a column modifier. The modifier can be the same or a different modifier from what was used in the second view.
4. Your SQL query subsets the third view with a WHERE clause using a variable that was defined with a column modifier

Scenario 3:

1. You create an SQL view that performs a UNION on two tables.
2. You create a second SQL view where the following is true:
   • The first view and the original table referenced in the first view are joined using an INNER JOIN with an ON clause, a NATURAL INNER JOIN, or by coding the INNER JOIN using a comma between the tables rather than using INNER JOIN syntax.
   • At least one of the variables used in the join is defined with a modifier including LENGTH=, LABEL=, INFORMAT=, and FORMAT=.
3. You create a third SQL view where the following is true:
   • The query references the second view.
   • The same join variables are defined with a column modifier. The modifier can be the same or different modifier from what was used in the second view.
4. Your SQL query subsets the third view with a WHERE clause using a variable that was defined with a column modifier

You can use one of the following workarounds for this issue:

• Create intermediate tables rather than views.
• Remove the column modifiers in at least one of the intermediate views for the variable that you are subsetting on. 

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.4_M7 is available at: