CODEWITHSUNDEEP
sqlsql-serversasproc-sql
vanellope1
vanellope1

Reputation: 43

Using SUBSTR and CHARINDEX into PROC SQL (SAS)

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!

Upvotes: 0

Views: 14351

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Try this

Upvotes: 3

Related Questions