topic Re: substr only the address line in SAS Programming

substr only the address line

monikka1991 — Fri, 30 Nov 2018 07:58:53 GMT

have to look from the last by space delimiter and remove the last three strings WHICH IS COUNTRY , ZIPCODE,STATE CODE

I HAVE

2001 P LARRY MN MINNESOTA MN 606123714 US

345 R TEENA TR FRANCE ST TN 60612345 UK

WHAT I WANT:

2001 P LARRY MN MINNESOTA

345 R TEENA TR FRANCE ST

Re: substr only the address line

Kurt_Bremser — Fri, 30 Nov 2018 08:16:58 GMT

Brute force attack:

do i = 1 to countw(charvar) - 3;
  tempvar = catx(' ',tempvar,scan(charvar,i));
end;
charvar = tempvar;
drop i tempvar;

For tested code, provide example data in a data step with datalines.

Re: substr only the address line

learsaas — Fri, 30 Nov 2018 09:11:35 GMT

	POSION=prxmatch('m/(\S+\s+\S+\s+\S+)$/',trim(text));
	if POSION>1 then result=substr(text,1,POSION-1);

Re: substr only the address line

monikka1991 — Fri, 30 Nov 2018 08:54:38 GMT

hi,

I tried your approach, but for countw function it gives me warning .

data A;
INPUT ADDRESS $50.;
DATALINES;
2001 P LARRY MN MINNESOTA MN 606123714 US
345 R TEENA TR FRANCE ST TN 60612345 UK
RUN;

DATA B;
SET A;
do i = 1 to countw(ADDRESS) - 3;
tempvar = catx(' ',tempvar,scan(ADDRESS,i));
end;
charvar = tempvar;
drop i tempvar;
RUN;

Re: substr only the address line

Kurt_Bremser — Fri, 30 Nov 2018 09:00:01 GMT

Add a statement that forces tempvar to be created as character:

data b;
set a;
length tempvar $50;
do i = 1 to countw(address) - 3;
  tempvar = catx(' ',tempvar,scan(address,i));
end;
charvar = tempvar;
drop i tempvar;
run;

Re: substr only the address line

RW9 — Fri, 30 Nov 2018 09:04:57 GMT

That second example row does not look right. We do not have "zipcode's" in the UK, nor "states". What I would suggest, as its obviously US data you are using, is to find the first numeric, then substr to that -3, something like:

data want;
  set have;
  want=substr(have,1,findc(have,"","d")-3);
run;

Re: substr only the address line

monikka1991 — Fri, 30 Nov 2018 12:07:36 GMT

I have attached input excel

PROC IMPORT OUT= HAVE
DATAFILE= "\LOCATION\INPUT.XLSX"
DBMS=EXCEL REPLACE;
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

Need to remove city state , zip from address field . If I go by position of space placed , there would be a problem when city is having space in between. (example : new York)

So I have tried below method to derive only address

data want(DROP=I ADDRESS COMB);
set have ;
length hcp_address_1 $50.;
comb = cat(strip(City)," ",strip(STATE)," ",strip(ZIP));
do i = 1 to countw(address) - countw(comb);
hcp_address_1 = catx(' ', address_1,scan(address,i-1));
end;
run;

Re: substr only the address line

Ksharp — Fri, 30 Nov 2018 14:08:07 GMT

data have;
input x $60.;
call scan(x,-3,p,l,' ');
want=substr(x,1,p-1);
drop p l;
cards;
2001 P LARRY MN MINNESOTA MN 606123714 US
345 R TEENA TR FRANCE  ST TN 60612345 UK
;
run;

proc print;run;