SAS prxparse Function

[cmoore]

Hi,

I am wanting to seperate out client names from a lengthy bit of text based on keywords that they come after. The code that I have been using in dev is below:

data have;
infile cards truncover;
input have_str $500.;
cards;
Firm A advises on the Volkswagen settlement with US authorities regarding emissions from diesel engines Firm A advises The Carlyle Group on its $3.2bn acquisition of Atotech advising Coca Cola
;
run;

data want;
set have;
retain _prxid;

/*advised*/
if _n_=1 then
_prxid=prxparse('/(\badvised)[^A-Z]{0,10}(([A-Z]\w+\s*\b)+)/');
if prxmatch(_prxid, have_str) then do;
client1=prxposn(_prxid, 2, have_str);
end;;

/*advises*/
if _n_=1 then
_prxid=prxparse('/(\badvises)[^A-Z]{0,10}(([A-Z]\w+\s*\b)+)/');
if prxmatch(_prxid, have_str) then do;
client2=prxposn(_prxid, 2, have_str);
end;;

/*advising*/
if _n_=1 then
_prxid=prxparse('/(\badvising)[^A-Z]{0,10}(([A-Z]\w+\s*\b)+)/');
if prxmatch(_prxid, have_str) then do;
client3=prxposn(_prxid, 2, have_str);
end;;
run;

There are multiple keywords such as "advising" or "advised" that I'm interested in. When the same keyword "advises" appears more than once in the same sentence then the data isn't populated. Any ideas please?

[cmoore]

The 3 outputs I want are; Client1=Volkswaggen, Client2=The Caryle Group and Client3=Coca Cola

[Ksharp · Accepted solution]

data have;
infile cards truncover ;
input have_str $500.;
cards;
Firm A advises on the Volkswagen settlement with US authorities regarding emissions from diesel engines Firm A advises The Carlyle Group on its $3.2bn acquisition of Atotech advising Coca Cola
;
run;

data want;
 set have;
 temp=have_str;
 p=prxmatch('/\b(advises|advised|advising)\b/i',temp);

 do while(p);
  temp=substr(temp,p);
  temp=substr(temp,findc(temp,' ')+1);
  name=catx(' ',scan(temp,1,' '),scan(temp,2,' '),scan(temp,3,' '));
  output;
  p=prxmatch('/\b(advises|advised|advising)\b/i',temp);
 end;

 drop temp p;
 run;

[FriedEgg]

filename ft15f001 temp;
data have;
infile ft15f001;
input text $194.;
parmcards;
firm a advises on the volkswagen settlement with us authorities regarding  emissions from diesel engines firm a advises the carlyle group on its  $3.2bn acquisition of atotech advising coca cola
;
run;

data want;
	prxid=prxparse('/\badvis(?:es|ed|ing)\b\s+((\b\w+\b\s*){1,3})/i');
	do until (eof);
		set have end=eof;
		start=1;
		stop=length(text);
		call prxnext(prxid, start, stop, text, position, length);
		do while (position > 0);
			call prxposn(prxid, 1, start, length);
			match=substr(text, start, length);
			output;
			call prxnext(prxid, start, stop, text, position, length);
		end;
	end;
run;

[cmoore]

Hi,

Thanks for the solutions @Ksharp you have sent me they are very useful. Just a final question in this area. In some data cells I have the following raw data:

"Deutsche Bank, Bank of America, Merrill Lynch and Morgan Stanley on National Grid's £3.3 billion equity offering"

They have no keywords associated with them such as 'advised' like the example. I am wanting to pull out of the data 'Deutsche Bank', 'Bank of America', 'Merrill Lynch' and 'Morgan Stanley' in exactly the same way as your solution. I'm not interested in obtaining 'National Grid'. Basically a firm has worked with the 4 firms I have highlighted but not National Grid. Are there any rules I can apply for any such scenarios that may come up in my data?

Thanks for all your help.

Chris

[cmoore]

Or where the keywords such as 'Advised' etc don't exist take start of sentence to the words "on" to isolate the clients I'm interested in. That's what I'm trying to embed into the current syntax you sent me

[Ksharp]

OK. I don't know if the following could work.

data have;
infile cards truncover ;
input have_str $500.;
cards;
Firm A advises on the Volkswagen settlement with US authorities regarding emissions from diesel engines Firm A advises The Carlyle Group on its $3.2bn acquisition of Atotech advising Coca Cola
Deutsche Bank, Bank of America, Merrill Lynch and Morgan Stanley on National Grid's £3.3 billion equity offering
;
run;

data want;
 set have;
 temp=have_str;
 p=prxmatch('/\b(advises|advised|advising)\b/i',temp);
if p=0 then do;
   name=substr(temp,1,prxmatch('/\bon\b/i',temp)-1);
   output;
end;
else do;
 do while(p);
  temp=substr(temp,p);
  temp=substr(temp,findc(temp,' ')+1);
  name=catx(' ',scan(temp,1,' '),scan(temp,2,' '),scan(temp,3,' '));
  output;
  p=prxmatch('/\b(advises|advised|advising)\b/i',temp);
 end;
end;
 drop temp p;
 run;