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 · Accepted Answer · 2016-11-07T15:49:04.093

3

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.

edited Nov 07 '16 at 15:49

answered Nov 07 '16 at 15:40

Tim Biegeleisen

502,043
27
286
360

Gah! How can I be so f'%¤ing blind and not see that the examples use `when`(as you say) and not `where`. I used `where` earlier in the code for a similar match when joining tables, which is why I got stuck with `where` after copying code. Thanks! – fileunderwater Nov 07 '16 at 15:44
The reason that I used `Contains` was to pass a series of strings (found in t2) to search for in (t1.test), to select or index records (which is also why I'm not joining). `Find` works, but searches individually for each match in t2, to the resulting table becomes length(t1)*length(t2) and requires some post-processing to remove duplicates. Thanks for the solution though. – fileunderwater Nov 11 '16 at 08:31
1

I upvoted directly after you posted the answer, and have now accepted – fileunderwater Nov 11 '16 at 08:33

Error when using Proc SQL and Case-statement

1 Answers1