DATA Step, Macro, Functions and more

How to get a substring?

Reply
Respected Advisor
Posts: 4,173

How to get a substring?

Hi all

I have a set of strings which end with "_".

I want to extract the substrings without the "_".

I don't know how the rest of the strings will look like.

I.e.
have: TESTDATAV1_20100110
want: TESTDATAV1

I would prefer to do it with SQL but I just can't figure out an elegant solution.

Any suggestions very welcome.


data have;
input TestString $20.;
datalines;
TESTDATAV1_20100110
TESTDATAV1_20100111
TESTDATAV2_20100110
TESTDATAV2_20100111
TEST_DATAV1_20100110
TEST_DATAV1_20100111
TEST_DATAV2_20100110
TEST_DATAV2_20100111
;
run;

proc sql;
select ???(TestString) as TestStringRoot
from have
;
quit;


Thanks
Patrick

P.S:
The real world problem I have to solve is creating views with data sets following the pattern as given in the example.
I.e: TESTDATAV1_20100110 and TESTDATAV1_20100111 should be selected to build the view TESTDATAV1.

The only thing I know for sure is that the library will only contain tables following the naming pattern (=end with "_").

I think I can solve this problem - but suggestions/experience from the field/a code example are very welcome.
Super Contributor
Posts: 474

Re: How to get a substring?

You can easily do this with the proper combination of text functions.

One possibility would be:
[pre]
proc sql noprint;
select reverse(scan(reverse(TESTSTRING),2,'_')) as TESTSTRING from HAVE;
quit;
[/pre]
See the following info about reverse and scan functions:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000245941.htm
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000214639.htm

Cheers from Portugal

Daniel Santos @ www.cgd.pt
Respected Advisor
Posts: 4,173

Re: How to get a substring?

Posted in reply to DanielSantos
Hi Daniel

The only thing I know for sure is that the string ends with "_".

There can be 1 to n underscores in the string.

The solution you proposed results for "TEST_DATAV1_20100110" in "DATAV1".

The result I need is "TEST_DATAV1".

It's about substracting a text pattern from a string - but only if the pattern is found at the end of the string.
PRXCHANGE() comes to my mind for something like this. I'm just asking myself if there isn't a simple solution which can be used within PROC SQL and I just don't get it.

Cheers
Patrick Message was edited by: Patrick
Super Contributor
Super Contributor
Posts: 3,174

Re: How to get a substring?

Consider using the FIND function with a negative third argument of some max value like -99 -- this resulting value (minus 1) will resolve your third argument to SUBSTR.

Scott Barry
SBBWorks, Inc.
Respected Advisor
Posts: 4,173

Re: How to get a substring?

Scott
That works.
Thanks
Patrick

data have;
input TestString $20.;
datalines;
TESTDATAV1_20100110
TESTDATAV1_20100111
TESTDATAV2_20100110
TESTDATAV2_20100111
TEST_DATAV1_20100110
TEST_DATAV1_20100111
TEST_DATAV2_20100110
TEST_DATAV2_20100111
;
run;

proc sql;
select substr(TestString,1,find(TestString,'_',-length(TestString))-1) as TestStringRoot
from have
;
quit;
Super Contributor
Posts: 474

Re: How to get a substring?

Hi Patrick.

OK, then, if it is possible to have more than a underscore, then switch the scan function to a combination of the substr/indexc functions, like this:
[pre]
reverse(substr(reverse(TESTSTRING),indexc(reverse(TESTSTRING),'_')+1))
[/pre]
Same logic, which is reverse the string to find the first underscore, trim, then reverse the result again.

Both FIND, INDEXC or even INDEX will work.
FIND/INDEX searchs a substring in a string, while INDEXC searchs a character (or list of) in a string.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Respected Advisor
Posts: 4,173

Re: How to get a substring?

Posted in reply to DanielSantos
Hi Daniel

Thanks. Scott and you are now in sync :-)

By the way: Since SAS 9 (I think) strings can also be processed backwards by many functions.
I.e:
reverse(scan(reverse(TESTSTRING),2,'_'))
Could also be written as:
scan(TESTSTRIN),-2,'_')

Cheers from Downunder
N/A
Posts: 0

Re: How to get a substring?

Even this will work..

proc sql;

select reverse(substr(reverse(trim(teststring)),10))
from have;

quit;

Reverse scan mentioned by you is perfect.

Jignesh
Ask a Question
Discussion stats
  • 7 replies
  • 610 views
  • 0 likes
  • 4 in conversation