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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.