topic Re: keep all the words before a certain word in SAS Programming

keep all the words before a certain word

Jarvin99 — Mon, 18 Apr 2022 13:53:32 GMT

I have long strings such as "James E Gooding will step down as Chairman and remain as Financial Expert of the Audit Committee" and "Sushi Danooo Kronick steps down as Chairman but remains as Member of the Investment and Finance Committee".

I want to keep all the words before the first word "and" or "but".

So, the results I want are "James E Gooding will step down as Chairman" and "Sushi Danooo Kronick steps down as Chairman".

I have tried scan and substr, but for scan, it seems there is a limit for extracting words. Only parts of the strings were copied. For substr, it does not work as the log said the word types were changed.

Thank you so much.

Re: keep all the words before a certain word

maguiremq — Mon, 18 Apr 2022 14:04:40 GMT

I wouldn't be surprised if I'm overlooking a more concise solution.

data have;
var = 'James E Gooding will step down as Chairman and remain as Financial Expert of the Audit Committee'; output;
var = 'Sushi Danooo Kronick steps down as Chairman but remains as Member of the Investment and Finance Committee'; output;
;
run;

data want;
	set have;
	want = trim(substr(var, 1, prxmatch('/and|but/', var) - 1));
run;

Re: keep all the words before a certain word

Jarvin99 — Mon, 18 Apr 2022 14:13:58 GMT

Hi, may I know why substr(description,1,find(description,'and','i')-1) does not work? What is the difference between find and prxmatch? Thank you so much.

Re: keep all the words before a certain word

Tom — Mon, 18 Apr 2022 14:17:13 GMT

You want FINDW() to look for a WORD.

data want;
  set have;
  loc1=findw(string,'and','i');
  loc2=findw(string,'but','i');
  if loc1 and loc2 then loc=min(loc1,loc2);
  else if loc1 then loc=loc1;
  else if loc2 then loc=loc2;
  if loc then want=substrn(string,1,loc-1);
run;

Re: keep all the words before a certain word

Jarvin99 — Mon, 18 Apr 2022 14:34:36 GMT

I see. Thank you for pointing out my mistake.

Re: keep all the words before a certain word

Jarvin99 — Mon, 18 Apr 2022 15:12:07 GMT

Hi,

Sorry I have a question again. I tried to add one more delimiter "to become".

data test;
set have;
loc1=findw(description,'and');
loc2=findw(description,'but');
loc3=findw(description,'to become');
if loc1 and loc2 then loc=min(loc1, loc2);
if loc1 and loc3 then loc=min(loc1, loc3);
if loc2 and loc3 then loc=min(loc2, loc3);
else if loc1 then loc=loc1;
else if loc2 then loc=loc2;
else if loc3 then loc=loc3;
if loc then description_1=substrn(description,1,loc-1);
run;

But, it does not work anymore. loc dose not return me the smallest number possible when comparing loc1 and loc2. What should I do? Thank you.

Re: keep all the words before a certain word

ballardw — Mon, 18 Apr 2022 15:44:32 GMT

@Jarvin99 wrote:
Hi, may I know why substr(description,1,find(description,'and','i')-1) does not work?

FIND will find the string even when embedded in a word:

data example; 
   string ="brand name information and other information";
   x = find(string,'and');
run;

Which returns 3 for the found position in the middle of "brand".

Sometimes you get away with it depending on the data but a relatively common string like "and" is in many words.

FINDW will restrict to things on "word" boundaries, which can be specified by additional parameters.

Hint: when you say "does not work" it often is a good idea to provide a working example and we can indicate what was found instead of what you expected.

Re: keep all the words before a certain word

Tom — Mon, 18 Apr 2022 17:01:38 GMT

@Jarvin99 wrote:

Hi,

Sorry I have a question again. I tried to add one more delimiter "to become".

data test;
set have;
loc1=findw(description,'and');
loc2=findw(description,'but');
loc3=findw(description,'to become');
if loc1 and loc2 then loc=min(loc1, loc2);
if loc1 and loc3 then loc=min(loc1, loc3);
if loc2 and loc3 then loc=min(loc2, loc3);
else if loc1 then loc=loc1;
else if loc2 then loc=loc2;
else if loc3 then loc=loc3;
if loc then description_1=substrn(description,1,loc-1);
run;

But, it does not work anymore. loc dose not return me the smallest number possible when comparing loc1 and loc2. What should I do? Thank you.

Your IF conditions are wrong when there are three instead of two search terms.

Perhaps a different algorithm would be easier to extend when there are more than two search terms.

data test;
  set have;
  loc=.;

  loc_next=findw(description,'and');
  if loc_next then loc=min(loc,loc_next);

  loc_next=findw(description,'but');
  if loc_next then loc=min(loc,loc_next);

  loc_next=findw(description,'to become');
  if loc_next then loc=min(loc,loc_next);

  if loc>1 then description_1=substr(description,1,loc-1);
run;