Convert SQL Code to SAS

[adhikra]

Hello.  I am new user to SAS and working on a project to migrate SQL Code to SAS. I have the following SQL Code below that I would like to get some help in converting to SAS:

Chartname is a field that contains the medical chart name.  Here is some sample data for ChartName:

ABCDEFV__3551843_20170526104401_ABC_196180535D_40308712.pdf                                       
ABCDCFVP__3551901_20170531000001_ABC_160346247A_401419457.pdf                                      
ABCDFV___3552215_20170602125701_ABC_172382989A_401566060.pdf

I need to get the data between the last "_"(underscore) and the ".pdf"

For example,

in line 1 I need "40308712                                       
In line 2, I need, "401419457"

In line 3, I need "401566060"

Here is the SQL Code that works below:

select ChartName,Left(right(ChartName, charindex('_', reverse(ChartName) + '_') - 1),CHARINDEX('.',right(ChartName, charindex('_', reverse(ChartName) + '_') - 1))) as ProvID

FROM Table1

Thank you in advance for your help.  Please ask any questions if I have not been clear in my issue,

[Astounding · Accepted solution]

You should be able to retrieve ProvID using this expression:

scan(ChartName, -2, '._')

You may need to add to the SELECT statement to assign a length to ProvID.

[adhikra]

Thank you your quick response.  I tried your code and was able to get the right answer.

[Cynthia_sas]

Hi:

  Luckily, the SCAN function allows you to break up a text string and extract a piece of the string. By default, SCAN starts at the left side of the string, but with a negative number, you can tell SCAN to start from the right side of the string. That's what this program does (after it makes some fake data to test with).

Hope this helps,

Cynthia

[adhikra]

Thank you your response.  Your code will work also.