select ???(TestString) as TestStringRoot
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.
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.
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:
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.