topic Re: Convert SQL Code to SAS in New SAS User

Convert SQL Code to SAS

adhikra — Fri, 25 Jan 2019 14:52:56 GMT

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,

Re: Convert SQL Code to SAS

Astounding — Fri, 25 Jan 2019 15:12:55 GMT

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.

Re: Convert SQL Code to SAS

Cynthia_sas — Fri, 25 Jan 2019 15:26:58 GMT

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

Re: Convert SQL Code to SAS

adhikra — Fri, 25 Jan 2019 21:36:51 GMT

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

Re: Convert SQL Code to SAS

adhikra — Fri, 25 Jan 2019 21:38:07 GMT

Thank you your response. Your code will work also.