BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to do pattern check in SAS Visual Anaslytics

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

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

## Re: How to do pattern check in SAS Visual Anaslytics

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 ))``````
3 REPLIES 3
Opal | Level 21

## Re: How to do pattern check in SAS Visual Anaslytics

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.

Rhodochrosite | Level 12

## Re: How to do pattern check in SAS Visual Anaslytics

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 ))``````
Obsidian | Level 7

## Re: How to do pattern check in SAS Visual Anaslytics

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
Discussion stats
• 3 replies
• 163 views
• 0 likes
• 3 in conversation