Extract alphanumeric sequence SAS/PROC SQL [duplicate]

Question

I'm converting some of my R code to SQL PROC for a program in SAS EG (V8.3.2.140), however I'm stuck. I need some help extracting a alphanumeric string from a column and make a new column with that alphanumeric string.

In R I'm using a package called rebus to create a patterns, subsequently I use mutate/str_extract to place it in a new column.

The pattern looks like this:

c( optional(ALPHA) %R% ALPHA %R% ALPHA %R% DGT %R% DGT %R% ALPHA %R% ALPHA %R% optional(ALPHA) %R% DGT %R% optional(DGT) %R% optional(ALPHA) )

So an optional character, followed by a character followed by a character followed by a digits, etc. It would look like this: ABC01DE02 or ABC01DE02A. I need both versions.

The position of the alphanumeric code in the string I'm trying to extract it from can vary, so using the position won't work. The characters will always be capital letter though.

Hope this provides enough information for a solution.

What does SQL have to do with this question?

Tom
– Tom

2024-06-18 18:26:28 +00:00
Commented Jun 18, 2024 at 18:26 — Tom
– Tom, Commented Jun 18, 2024 at 18:26

Stu Sztukowski · Accepted Answer · 2024-06-19 13:55:51Z

This sounds like a job for regex. The below regex should do it based on your code and description:

[A-Z]{0,3}\d{2}[A-Z]{2,3}\d{1,2}[A-Z]

regex101.com test

You'll want to do this with a data step rather than SQL:

data want; length string $25.; input string$; /* Calculate the regex id only once */ retain regexid; if(_N_ = 1) then regexid = prxparse('/[A-Z]{0,3}\d{2}[A-Z]{2,3}\d{1,2}[A-Z]?/'); /* If a match is found, extract the string: 1. Get the position and length of the match 2. Extract the match by position and length */ if(prxmatch(regexid, string)) then do; call prxsubstr(regexid, string, pos, len); extract = substr(string, pos, len); end; drop pos len regexid; /* In-line sample data */ datalines; KJ3828ADIJABC01DE02292LZ FJS9EO2ABC01DE02A92UJDDA ; run;

string extract KJ3828ADIJABC01DE02292LZ ABC01DE02 FJS9EO2ABC01DE02A92UJDDA ABC01DE02A

Collectives™ on Stack Overflow

Extract alphanumeric sequence SAS/PROC SQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related