topic Re: Use of Substr or Scan in SAS Data Management

Use of Substr or Scan

venkatnaveen — Wed, 20 Jan 2016 12:02:37 GMT

Hi I need some help. I have the variable like site with values

Site

CT-BENGALURU-J.P.NAGAR

CT-BENGALURU-ORION MALL

CT-BENGALURU-SOUL SPACE SPIRIT

CT-MUMBAI-GOREGAON-OBEROI MALL

CT-PUNE-ASCENT MALL

CT-PUNE-MSM PARANJAPE MALL-KARVE ROAD

I want output like

J.P.NAGAR

ORION MALL

SOUL SPACE SPIRIT

OBEROI MALL

ASCENT MALL

PARANJAPE MALL

Re: Use of Substr or Scan

Kurt_Bremser — Wed, 20 Jan 2016 12:16:33 GMT

I can't see a consistent rule here.

Usually it's the last "word" of those separatd by hyphens, but in the last line it is the second (and not even the whole second) and not the last. Without a consistent rule, no algorithm can be built.

Re: Use of Substr or Scan

LinusH — Wed, 20 Jan 2016 12:44:39 GMT

Looks like addresses, right?

Like @Kurt_Bremser said, there seems not be a clear rule.

That leaves us to do data standardization.

And I suspect that addresses are available in the SA Data Quality Base, India edition, contained in the data flux product(s).

Re: Use of Substr or Scan

Astounding — Wed, 20 Jan 2016 14:50:17 GMT

Here are some statements that approximate what you are trying to do:

new_site = scan(site, 3, '-');

new_site = scan(site, -1, '-');

The reason I say "approximate" is because you have rules in your head that are not part of the program. For example, consider:

CT-PUNE-MSM PARANJAPE MALL-KARVE ROAD

Why should the result be PARANJAPE MALL instead of MSM PARANJAPE MALL? You have some rules about that, but all of your rules have to be made known in order to incorporate them into the program.

Good luck.

Re: Use of Substr or Scan

Ksharp — Thu, 21 Jan 2016 05:52:43 GMT

data have;
input Site $80.;
length want $ 80;
pid=prxparse('/\w+\s+MALL/i');
if prxmatch(pid,site) then do;
 call prxsubstr(pid,site,p,l);
 want=substr(site,p,l);
 end;
 else want=scan(site,-1,'-');
drop p l;
cards;
CT-BENGALURU-J.P.NAGAR
CT-BENGALURU-ORION MALL
CT-BENGALURU-SOUL SPACE SPIRIT
CT-MUMBAI-GOREGAON-OBEROI MALL
CT-PUNE-ASCENT MALL
CT-PUNE-MSM PARANJAPE MALL-KARVE ROAD
;
run;

Re: Use of Substr or Scan

venkatnaveen — Thu, 21 Jan 2016 12:16:52 GMT

Thanks a lot.

Re: Use of Substr or Scan

venkatnaveen — Thu, 21 Jan 2016 12:19:18 GMT

Actually there is data inconsistency.