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
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 ))
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.
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 ))
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.