Help using Base SAS procedures

extract prov and postal code form address line

Accepted Solution Solved
Reply
Super Contributor
Posts: 401
Accepted Solution

extract prov and postal code form address line

[ Edited ]

Hi ,  what is the best way to extract porvince and postal code from an address line as:

Address_Var

TORONTO       ON  K9Z 9Z9

TORONTO       ON  M6E 3C6

LONDON        ON  N5V 3L1

TOTTENHAM     ON  L0G 1W0

 

and create 4 new fields.  Eg. City, prov and Postal_code and FSA (the 3 first digits of the postal code, eg. K9Z, M6E). 

 

Thanks.


Accepted Solutions
Solution
‎09-26-2016 04:57 PM
Trusted Advisor
Posts: 1,586

Re: extract prov and postal code form address line

[ Edited ]

It seems that each line is made of 4 strings: city , province , zip , fsa

anyhow there are probably cities made of two or even 3 strings,

in such case try next code:

 

data want;

        infile datalines;

        input address $60.;

        fsa = scan(address,-1);

        zip = scan(address,-2);

        province = scan(address,-3); 

        ix = index(address,province);

        city = substr(address,1,ix-1);

        drop ix;

run;

View solution in original post


All Replies
Solution
‎09-26-2016 04:57 PM
Trusted Advisor
Posts: 1,586

Re: extract prov and postal code form address line

[ Edited ]

It seems that each line is made of 4 strings: city , province , zip , fsa

anyhow there are probably cities made of two or even 3 strings,

in such case try next code:

 

data want;

        infile datalines;

        input address $60.;

        fsa = scan(address,-1);

        zip = scan(address,-2);

        province = scan(address,-3); 

        ix = index(address,province);

        city = substr(address,1,ix-1);

        drop ix;

run;

Super User
Posts: 19,876

Re: extract prov and postal code form address line

Do you have Cities that have two names? ie Niagara Falls?

Super Contributor
Posts: 401

Re: extract prov and postal code form address line

Yes, it's all Canada.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 483 views
  • 0 likes
  • 3 in conversation