topic Re: Working with Addresses in SAS Programming

Working with Addresses

andrewfau — Mon, 29 Jan 2018 21:04:59 GMT

My data set has the street number, prefix, street name, and suffix all together in a single variable. I am trying to separate the street number from the rest of the address. (For example, I want 3991 3rd ST to read 3991 in one variable and 3rd ST in the next variable.) I know I have to do some sort of formatting language at the first space in the column but am not sure how to do that.

Re: Working with Addresses

Reeza — Mon, 29 Jan 2018 21:25:05 GMT

You can start with SCAN() but I'd strongly consider doing some searching on LexJansen for some developed code and working off that. This is a non-trivial problem (sadly) and usually PRX is the best approach and the papers will cover topics you didn't mention such as Street vs St vs St. and other things.

Re: Working with Addresses

art297 — Mon, 29 Jan 2018 21:29:58 GMT

Yes, as @Reeza said, it can get quite complex. However, if you're only wanting to separate the number from the rest, you could use something like:

data have;
  informat address $80.;
  input address &;
  cards;
3991 3rd ST
;

data want (drop=_:);
  set have;
  call scan(address,1,_pos,_len);
  street_number=input(substr(address,_pos,_len),8.);
  street=left(substr(address,_len+1));
run;

Art, CEO, AnalystFinder.com

Re: Working with Addresses

andrewfau — Tue, 30 Jan 2018 16:08:23 GMT

I used your code and I was able to separate the street number for the first address in my list. But how would I do it for the entire variable? I have about 30,000 observations.

data palmbeachsales16;
informat own_addr1 $80.;
input own_addr1 &;
cards;
50257 CORKSCREW BLVD
;

data palmbeachaddr16 (drop=_:);
set palmbeachsales16;
call scan(own_addr1,1,_pos,_len);
street_number=input(substr(own_addr1,_pos,_len),8.);
street=left(substr(own_addr1,_len+1));
run;

Re: Working with Addresses

Reeza — Tue, 30 Jan 2018 16:12:18 GMT

data palmbeachaddr16 (drop=_:); 
set palmbeachsales16; <- change this to be YOUR data set name;

You're using the demo data rather than your own. Change the SET statement to refer to your data set.

Re: Working with Addresses

andrewfau — Tue, 30 Jan 2018 16:40:14 GMT

Like this? But how do I generalize it to all the addresses in the set?

data palmbeachaddr16 (drop=_:);
set palmbeachsales16;
call scan(own_addr1,1,_pos,_len);
street_number=input(substr(own_addr1,_pos,_len),8.);
street=left(substr(own_addr1,_len+1));
run;

Re: Working with Addresses

Reeza — Tue, 30 Jan 2018 16:42:51 GMT

@andrewfau wrote:

Like this? But how do I generalize it to all the addresses in the set?

What do you mean by that? Once you refer to your own address it will extract all the numbers. What are you expecting and what are you getting?

Re: Working with Addresses

andrewfau — Tue, 30 Jan 2018 17:00:22 GMT

The example code had the example address in it. I have a dataset with about 30,000 addresses and I am trying to separate the streetnumber from the street name in each to make them separate variables. Is there a way to apply that code to all the addresses in that variable rather than just one?

Re: Working with Addresses

Reeza — Tue, 30 Jan 2018 17:03:34 GMT

Did you run the new code which refers to your data set and your address? It will do that automatically, that's how SAS works by default, it processes all rows within the data step.

If it's not working, post your code, log and explain what's not working in detail. Not working isn't informative.

Re: Working with Addresses

andrewfau — Tue, 30 Jan 2018 17:29:37 GMT

I figured it out. I was making the simple mistake of not bringing in my whole dataset. Sorry. I'm still new at this. It is working. Thank you.

Re: Working with Addresses

tomcmacdonald — Tue, 30 Jan 2018 18:16:56 GMT

You could try regular expressions

data foo;
	infile datalines delimiter=',';
	informat raw $varying1024.;
	input raw $;
	datalines;
3991 3rd ST
;
run;


proc sql;
	select
		prxchange('s/(\d+)(?: )(\w+)/$1/', -1, raw) as house_number
		,prxchange('s/(\d+)(?: )(\w+)/$2/', -1, raw) as street
	from foo;
quit;