How do I retrieve records containing alphanumeric characters, excluding unwanted results

Reply
Occasional Learner
Posts: 1

How do I retrieve records containing alphanumeric characters, excluding unwanted results

Database contains the following records:

A4

B1

B200

B250X

B300X

B4000

B50

C1

I want to retreive all records starting with 'B', whose numeric portion is between 1 and 1000, but does not end with a letter?

Need help with SQL statement to do this

 

Desired result:

B1, B200, B50

 

Thanks

Respected Advisor
Posts: 3,124

Re: How do I retrieve records containing alphanumeric characters, excluding unwanted results

data have;
input var$;
cards;
A4
B1
B200
B250X
B300X
B4000
B50
C1
;

data want;
set have;
if prxmatch('/^B\d{1,4}$/',strip(var));
if 1<= compress(var,,'kd') <= 1000;
run;
Respected Advisor
Posts: 3,890

Re: How do I retrieve records containing alphanumeric characters, excluding unwanted results

I like @Haikuo's RegEx solution. Here another coding option.

data want;
  set have;
  if substr(var,1,1)='B' and 1<=input(substr(var,2),?? best32.)<= 1000;
run;
Ask a Question
Discussion stats
  • 2 replies
  • 204 views
  • 0 likes
  • 3 in conversation