Datastage equivalent for 'Scan' function of SAS

[dhanashreepanse]

DS Gurus,

We are having a peculiar problem where in we want to implement the equivalent of the SCAN function (available in SAS) in Datastage PX.
The SAS code is : state = scan(InputString,-1)
i.e from the InputString it reads the rightmost word and assigns to the variable state.

As no delimiter is specified, the SCAN function would use the following default delimiters : blank . < ( + & ! $ * ) ; ^ - / , % |

For example
1. If the string is ABCD EF (NY the function will return NY
2. If the string is ABCDEF +GH,CA the function will return CA


Now the issue in Datastage is that we tried to use Field and Index functions but the logic is getting too complicated.

Could somebody please suggest any other option/functions.

Thanks in advance.

Regards,
Dhanashree

[ray.wurlod]

There's nothing out of the box. You would need to write your own. If the number of delimiters is limited to one, then you can find the rightmost delimited substring readily using a combination of Field() and DCount() functions.