Using SUBSTR and CHARINDEX into PROC SQL (SAS)

0
votes

I have written out a SQL query for Microsoft SQL Server, but now I am trying to convert it into SAS PROC SQL and I am running into issues.

Data:

╔══════════╦══════════╦════════╗
║   Name   ║ Question ║ Answer ║
╠══════════╬══════════╬════════╣
║ Jane Doe ║ Q1       ║ Q1-Yes ║
║ John Doe ║ Q1       ║ Q1-No  ║
╚══════════╩══════════╩════════╝

I want to get rid of the "Q1-" in the Answer column and output it as just "Yes" or "No".

In SQL, the code I used is:

SUBSTRING (AnswerTXT, CHARINDEX('-', AnswerTXT)+1, LEN (AnswerTXT)) as 'Answer'

In SAS, I figured out I need to change it in PROC SQL. This is what I got so far:

SELECT SUBSTR(AnswerTXT,

I have tried Index and Scan and keep getting this type of error:

CLI describe error: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]'scan' is not a 
   recognized built-in function name.

Thanks!

1
sqlsql-serversasproc-sql
will you always have 3 characters you need to strip off, in the answer column? – Vamsi Prabhala
Why not just use pass-through and write the SQL using native T-SQL? – Gordon Linoff
@vkp: Sadly no, sometimes it will be more than 3 characters. – vanellope1
That error sounds like it is a pass through query, @GordonLinoff ... or else this is something else odd. – Joe
@GordonLinoff I think the question didn't include the correct syntax. – Joe

1 Answers

2
votes
SUBSTR(answer, INDEX('-',answer)+1, length(answer))

Try this