DATA Step, Macro, Functions and more

Is there an ORACLE/SQL equivalent to the SAS SCAN command?

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,934
Accepted Solution

Is there an ORACLE/SQL equivalent to the SAS SCAN command?

I am using SAS PROC SQL to extract data from an ORACLE data base. I believe that I need to find an ORACLE equivalent to the SAS SCAN command, since the SAS SCAN command doesn't work in the part of the code which is transferred to and executed in the ORACLE database.

 

For example, if the variable named ID has a value of

 

abc_de_fghijk

 

then in a SAS data step the command str=scan(id,2,'_'); will extract the text string 'de' from ID.

 

Is there an equivalent command that I can use in ORACLE? THanks!


Accepted Solutions
Solution
‎10-28-2015 11:38 AM
Super User
Super User
Posts: 7,997

Re: Is there an ORACLE/SQL equivalent to the SAS SCAN command?

Posted in reply to PaigeMiller

You could have a look at:

https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

The regexp_substr() function seems to be a good fit.

View solution in original post


All Replies
PROC Star
Posts: 1,325

Re: Is there an ORACLE/SQL equivalent to the SAS SCAN command?

Posted in reply to PaigeMiller

I haven't tried it, but looks like you may need to combine INSTR()  or CHARINDEX() to look up the position of the ith underscore, with SUBSTRING().

 

 

Solution
‎10-28-2015 11:38 AM
Super User
Super User
Posts: 7,997

Re: Is there an ORACLE/SQL equivalent to the SAS SCAN command?

Posted in reply to PaigeMiller

You could have a look at:

https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

The regexp_substr() function seems to be a good fit.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 1981 views
  • 0 likes
  • 3 in conversation