topic Re: Cleanup address data set in SAS Programming

Cleanup address data set

SNG1 — Mon, 08 Oct 2018 16:13:57 GMT

Hi All,

I have a data set which has addresses that are all messy, it looks line for example -

2303 215 Queen St E

123 21 Apple Rd

02 1 Yonge St E

64 Ardwick Blvd

16 Kelman Crt

I want the result as follows -

I want to exclude any observation that has multiple spaces inform of an alphabet, that is I only want 64 Ardwick Blvd and 16 Kelman Crt in my dataset and exclude everything else.

Also id there are two spaces in front of the alphabet then I need to keep this just in case if there is any observation like -

64 Ardwick Blvd (i.e. 2 spaces after 64) or 64 Ardwick Blvd (i.e. 3 spaces after 64) then I need to include these as 64 Ardwick Blvd.

Any help is much appreciated...

Thanks!

Re: Cleanup address data set

DatNinjaTurtle — Mon, 08 Oct 2018 21:04:05 GMT

data new;
	infile datalines;
	input id address $20.;
	cards;
2303 215 Queen St E
123 21 Apple Rd
02 1 Yonge St E
64 Ardwick Blvd
16 Kelman Crt
;
run;

data new;
	set new;
	X=scan(address, 3);

	if X ne'' then
		delete;
run;

Hopefully, this will solve your problem.

Re: Cleanup address data set

ChrisNZ — Mon, 08 Oct 2018 21:14:10 GMT

Like this?

data HAVE;
 input ADDRESS $20.;
 cards;
2303 215 Queen St E
123 21 Apple Rd
02 1 Yonge St E
64   Ardwick Blvd
16 Kelman Crt
run;
data WANT;
  set HAVE;
  ADRESS=compbl(ADDRESS);
  if prxmatch('/[^a-zA-Z\s]+?\s[^a-zA-Z\s]+?\s\w/',ADDRESS) then delete;
run;

NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 2 observations and 3 variables.

Re: Cleanup address data set

Ksharp — Tue, 09 Oct 2018 13:38:40 GMT

If I understood what you want.

data HAVE;
 input ADDRESS $20.;
 cards;
2303 215 Queen St E
123 21 Apple Rd
02 1 Yonge St E
64   Ardwick Blvd
16 Kelman Crt
run;
data WANT;
  set HAVE;
  ADRESS=compbl(ADDRESS);
  if countc(strip(substr(ADDRESS,findc(ADDRESS,' '))),' ')=1;
run;