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,
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.
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.
Thank you your quick response. I tried your code and was able to get the right answer.
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
Thank you your response. Your code will work also.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.