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-07 15:49:04Z

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 7, 2016 at 15:49

answered Nov 7, 2016 at 15:40

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fileunderwater Over a year ago

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 Over a year ago

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 Over a year ago

I upvoted directly after you posted the answer, and have now accepted

Collectives™ on Stack Overflow

Error when using Proc SQL and Case-statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related