scan function within SQL where statement

Accepted Solution Solved
Reply
Contributor AD
Contributor
Posts: 31
Accepted Solution

scan function within SQL where statement

I have the following code, and it's giving me an error with the scan statement, but I don't understand why.

% Let RefExamID = 100;

% Let RefFormID= 15 16 18;

Proc SQL;

Select Question_ID

     From TestData t1

   Where t1.Examination_ID = &RefExamID

         AND t1.Form_ID = scan(&RefFormID, 1);

Quit;

Here is the portion of the log statement that has the error:

19                 AND t1.FORM_ID = scan(&RefFormID, 1)
NOTE: Line generated by the macro variable "REFFORMID".
19         15 16 18
              __
              22
              200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=,
              <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET,
              LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. 

ERROR 200-322: The symbol is not recognized and will be ignored.


Accepted Solutions
Solution
‎04-25-2014 12:28 PM
Super User
Super User
Posts: 6,502

Re: scan function within SQL where statement

What is it that you want to do?

Since the index is hardcoded to 1 you should be able to convert to using macro scan function instead.

Your Code:

  AND t1.FORM_ID = %scan(&RefFormID, 1)

What SAS Sees:

  AND t1.FORM_ID = 15

View solution in original post


All Replies
Super User
Posts: 17,912

Re: scan function within SQL where statement

Your Code:

scan(&RefFormID, 1)

What SAS Sees:

scan(15 16 18, 1)

What SAS should see:

scan("15 16 18", 1)

So add quotation marks:

scan("&RefFormID", 1)

Contributor AD
Contributor
Posts: 31

Re: scan function within SQL where statement


Thanks, but now I'm getting a new error.

19                 AND t1.FORM_ID = Scan("&RefFormID",1);

ERROR: Expression using equals (=) has components that are of different data types.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Solution
‎04-25-2014 12:28 PM
Super User
Super User
Posts: 6,502

Re: scan function within SQL where statement

What is it that you want to do?

Since the index is hardcoded to 1 you should be able to convert to using macro scan function instead.

Your Code:

  AND t1.FORM_ID = %scan(&RefFormID, 1)

What SAS Sees:

  AND t1.FORM_ID = 15

Contributor AD
Contributor
Posts: 31

Re: scan function within SQL where statement

Thanks, Tom. That worked beautifully. Thanks to everyone else as well.

Trusted Advisor
Posts: 1,204

Re: scan function within SQL where statement

Try this one

AND t1.Form_ID=input(scan("&RefFormID", 1),best32.);

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 298 views
  • 6 likes
  • 4 in conversation