Error when using Proc SQL and Case-statement

Question

I'm trying to use Proc SQL and a case-statement to test for a condition and add an index. However, I cannot get the case-where statement to work even though the syntax seems to match the examples I've seen. Using the code below I get the following error:

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, WHEN, ^, ~.

Proc SQL;
    Create table table_ix AS
    Select t1.*,
            Case
                Where UPCASE(t2.test) Contains UPCASE(TRIM(t2.key)) Then 1
                Else 0
            end as index
    From Table1 AS t1, Table2 AS t2;

QUIT;

From what can see in Help, my statement matches the examples. This is probably simple to solve and a minor overlook on my part, but I cannot get it to work (for instance, I've tried matching a single string to see if the reference to a separate table is the issue, e.g. ...Contains UPCASE("Teststring")....
Any suggestions?

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-11-07T15:40:32

The ANSI standard syntax for a CASE expression, which Proc-SQL follows AFAIK, is CASE WHEN, not CASE WHERE. Try this query:

CREATE TABLE table_id AS
SELECT t1.*,
       CASE WHEN FIND(t2.test, TRIM(t2.key), 'i') GE 1 THEN 1 ELSE 0 END AS index
FROM Table1 AS t1, Table2 AS t2;

Note: I replaced your call to CONTAINS with the FIND function, because this SAS reference mentions that CONTAINS is only available in a WHERE clause.

By the way, you might have intended to add a join condition to tables one and two. Currently, you are doing an open-ended cross join between them. But hopefully my query will resolve the error in your CASE expression.

Error when using Proc SQL and Case-statement

1 Answers