- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you have Cities that have two names? ie Niagara Falls?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, it's all Canada.