BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
podarum
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

3 REPLIES 3
Shmuel
Garnet | Level 18

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;

Reeza
Super User

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

podarum
Quartz | Level 8

Yes, it's all Canada.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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