How to do pattern check in SAS Visual Anaslytics

[anuragraishines]

Hi,

I have one requirement where I have to identify a pattern in column and trim the column accordingly and it should be dynamic in nature.

So the ask here is to remove all the characters, numbers and underscore from the last of the string till the eight digit numbers are removed. The length of string may vary but pattern remains same.So it will be something like this as below :-

QH1234AGA3BARIALBA12345678M_A => QH1234AGA3BARIALBA

QL4321AGARIC_ANNBC87654321M_A = > QL4321AGARIC_ANNBC

QL0000LAGRICCQQ_BC99998888ABC_A => QL0000LAGRICCQQ_BC

So the highligted part will always have 8 digit number followed by characters which can be of any length than followed by _ (Underscore) and than character which can be of any length.

So there is a pattern in string which I am able to achieve this through a sas perl function (prxchange('s/[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]*[A-Z]*_*[A-Z]//', -1, Col1)) but don't know how to achieve it in SAS VA.

Thanks,

Anurag

[SASKiwi]

Why don't you do this using PRXCHANGE when preparing your data prior to loading it into SAS VA? There's no problem running "normal" SAS code on a SAS VA platform as part of data preparation.

[acordes · Accepted solution]

To fix this already in SAS Studio / prepare data is probably much better. You know the solution with the prx approach. 

But you can achieve it in VA:

Create a new calculated item and replace 'str' with your variable that holds the string. 

Substring('str'n, 1, ( FindString('str'n, Reverse(Substring(
Reverse(RemoveChars(LowerCase('str'n),
'abcdefghijklmnopqrstuvwxyz_')), 1, 8))) - 1 ))

[anuragraishines]

Thanks acordes for the input it was exactly what I was looking to implemet. Have to add few more logic on top of it and it will be done. The reason I am trying to avoid in SAS Studio is that it is the only derived column and rest is a flat table hence wanted to avoid that extra layer and other orchasteration