CODEWITHSUNDEEP
sassubstr
MLPNPC
MLPNPC

Reputation: 525

Deleting everything after the last comma SAS

I have an address field that has address similar to:

1 High Street, London, Uk, L1 6YR 
Flat 2, 35 Second Street, London,Greater London, L14 8FG

I would like to get rid of everything after the last comma so that I end up with:

   1 High Street, London, Uk
   Flat 2, 35 Second Street, London,Greater London

I tried to do this:

DATA WANT;
SET HAVE;
ADDRESS_NEW = SCAN(ADDRESS, -1, ",");
RUN;

But this just returns the post code:

L1 6YR
L14 8FG

Upvotes: 1

Views: 711

Answers (2)

data _null_
data _null_

Reputation: 9109

FindC with the B option.

24   data _null_;
25      input address $80.;
26      put / address=;
27      l = findc(address,',','b');
28      if l then address = substrn(address,1,l-1);
29      put  (_all_)(=);
30      datalines;


address=1 High Street, London, Uk, L1 6YR
address=1 High Street, London, Uk l=26

address=Flat 2, 35 Second Street, London,Greater London, L14 8FG
address=Flat 2, 35 Second Street, London,Greater London l=48

address=Argy Bargy Lane
address=Argy Bargy Lane l=0

address=London, Uk
address=London l=7

Upvotes: 1

Richard
Richard

Reputation: 27498

You can use PRXCHANGE to modify a text value meeting your desired criteria. This example uses the regex pattern s/,[^,]*$// to replace the found end part with nothing.

data have;
input address $80.;
datalines; 
1 High Street, London, Uk, L1 6YR 
Flat 2, 35 Second Street, London,Greater London, L14 8FG
Argy Bargy Lane
London, Uk
run;

data want;
  set have;
  address = prxchange("s/,[^,]*$//", 1, address);
run;

Upvotes: 1

Related Questions