BookmarkSubscribeRSS Feed
Patrick
Opal | Level 21
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.
7 REPLIES 7
DanielSantos
Barite | Level 11
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
Patrick
Opal | Level 21
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Patrick
Opal | Level 21
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;
DanielSantos
Barite | Level 11
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
Patrick
Opal | Level 21
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
deleted_user
Not applicable
Even this will work..

proc sql;

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

quit;

Reverse scan mentioned by you is perfect.

Jignesh

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1694 views
  • 0 likes
  • 4 in conversation