DATA Step, Macro, Functions and more

Help in Instr function

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Help in Instr function


This code works in SQL developer, but getting error in SAS
proc sql;
select
case WHEN Name LIKE '%' || substr(name2,instr(name2,' ',-1)+1)|| '%' then 'Y' else 'N' end as survey1
from x;
run;

ERROR: Function INSTR could not be located.
ERROR: Expression using addition (+) requires numeric types.


Accepted Solutions
Solution
‎08-11-2017 11:39 AM
Super User
Super User
Posts: 7,988

Re: Help in Instr function

Posted in reply to Kalai2008

Yes, SAS implements ANSI SQL, not Oracle additional components.

Your case can be re-defined but would need to see some of the data it is working on to get an idea of what you want.  I would guess at:

case when index(name,name2) > 0 then "Y" else "N" end as survey1

 

 

Note the above uses a SAS function index() so wouldn't work if you pass it through to the database, however if you did that the original query would work anyways.

View solution in original post


All Replies
Solution
‎08-11-2017 11:39 AM
Super User
Super User
Posts: 7,988

Re: Help in Instr function

Posted in reply to Kalai2008

Yes, SAS implements ANSI SQL, not Oracle additional components.

Your case can be re-defined but would need to see some of the data it is working on to get an idea of what you want.  I would guess at:

case when index(name,name2) > 0 then "Y" else "N" end as survey1

 

 

Note the above uses a SAS function index() so wouldn't work if you pass it through to the database, however if you did that the original query would work anyways.

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 292 views
  • 3 likes
  • 2 in conversation