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

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
acordes
Rhodochrosite | Level 12

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 ))

View solution in original post

3 REPLIES 3
SASKiwi
PROC Star

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
Rhodochrosite | Level 12

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
Quartz | Level 8
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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 587 views
  • 0 likes
  • 3 in conversation