Identifying correct length and format of postal codes

[shahm]

Hello,

I have over 17000 observations in a dataset and one of the Variables is postal_code. 

I need to create a dataset which has only the correct postal codes i.e K2C0J8.

the postal code should be six digits as in the example above, have no spaces, no special characters and the 2nd, 4th and the 6th spaces should be numbers and the first, third and fifth places alphabets. 

here is my code so far: 

data CORRECTPOSTAL NOPOSTAL;
set Y;
if length(compress(postal_code,"-/+~! )( "))=6 and PRXMATCH(PRXPARSE("/\w\d\w\d\w\d/i"),compress(postal_code,"- ")) then do;
POSTAL=compress(postal_code,"-/+~! )( ");
output CORRECTPOSTAL;
end;
else output NOPOSTAL;
run;

this does the job alright but misses out on some observations and I dont know why. 

Thanks,

M

[tarheel13]

can you post sample data? 

[ballardw]

Post some examples of the values that don't return the correct result.

If you both incorrect CORRECTPOSTAL and NOPOSTAL then some of each and which result you are getting.

Note: It is a very good idea to post code in either a code or text box opened using the "running man" or </> icon appearing above the message window. The forum main message windows will reformat pasted text and may result in code changes, which could be pretty significant when dealing with text manipulation code.

[acordes]

you can do with one line only:

if prxmatch('/\b([a-zA-Z])\d([a-zA-Z])\d([a-zA-Z])\d\s/', postal_code);

[shahm]

Thank you all for your prompt responses! My code did identify the missing and incorrect postal codes accurately. Any ideas where can I brush up my Regular expressions. 

Sincerely,

M

[acordes]

https://regex101.com/ 

https://www.regular-expressions.info/tutorial.html 

https://support.sas.com/rnd/base/datastep/perl_regexp/regexp-tip-sheet.pdf