Perl Regular Expressions in SAS

[Romeo1]

/*I want write a general expression for a code with this characteristics bellow:
9 NUMBERS NUMBERS FROM (0-9), 2 LETTES FROM (A-Z) AND 3 NUMBERS FROM (0-9), e.g: 000028393OE123
The code below is not working for the expression that i did.c_bi: SSSSSSSSSSA030 - WRONG R020782/492008 - WRONG R010114/970308 - WRONG R006767/653308 - WRONG 000164063BE033 - CORRECT 000264063LA021 - CORRECT */

proc sql;
create table a as
select 	c_bi, 
		prxmatch('/^[0-9]{9}[a-zA-Z]{2}[0-9]{3}',c_bi) 	as c_bi_dum,
from pdc.pdc_cli_bancarizacao 
where length(c_bi)=14 and 
c_nif='' and 
c_passaporte='' and 
cartao_residencia='' and 
cedula_pessoal='' and	
outro_documento='' and 	
numero_documento=''
order by c_bi desc;
run;

[maguiremq]

People are going to want you to post some example data. Include what you have, fictitious or not, and what you expect.

[Romeo1]

Hi,

already include the expression called "c_bi" for wrong and correct expression. I think with that they can help me.

[ErikLund_Jensen]

Hi @Romeo1 

There are several issuses with your code.

1. A Proc SQL step is terminated with a QUIT statement, nor a RUN statement.
2. In Proc SQL a comma is useed to separate columns, so a comma after the last variable will generate a syntax error.
3. A perl regular expression must be enclosed in /.../, the last is missing in your code.

Your expression works as expected When these errors are corrected. I suggest a couple of modifications as shown in the following code.

The i modifier at the end of the regular expression instructs prxmatch to ignore case, so both BE and be is correct.

The dollar sign in the expression marks end of data just as ^ marks beginning of data. Combined with trimming of the ingoing variable it ensures that a match is found only if the string has exact 14 characters as specified in the expression.

data have; 
	input c_bi $20.;
datalines;
SSSSSSSSSSA030
R020782/492008
R010114/970308
R006767/653308
000164063BE033
000164063be033
000264063LA021
000264063LA0212
000264063LA02
;
run;

proc sql;
	create table want as
		select 	
			c_bi, 
			prxmatch('/^[0-9]{9}[a-z]{2}[0-9]{3}$/i',trim(c_bi)) as c_bi_dum
from have;
quit;