topic Re: Identifying correct length and format of postal codes in SAS Programming

Identifying correct length and format of postal codes

shahm — Thu, 20 Jan 2022 20:49:14 GMT

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,

Re: Identifying correct length and format of postal codes

tarheel13 — Thu, 20 Jan 2022 21:34:13 GMT

can you post sample data?

Re: Identifying correct length and format of postal codes

ballardw — Thu, 20 Jan 2022 21:44:05 GMT

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.

Re: Identifying correct length and format of postal codes

acordes — Fri, 21 Jan 2022 00:44:16 GMT

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);

Re: Identifying correct length and format of postal codes

shahm — Fri, 21 Jan 2022 13:37:43 GMT

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,

Re: Identifying correct length and format of postal codes

acordes — Fri, 21 Jan 2022 13:56:14 GMT

https://regex101.com/

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

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