topic Re: How do I extract a keyword and subsequent text from a string and... in SAS Programming

How do I extract a keyword and subsequent text from a string and...

Ksmit — Mon, 28 Sep 2020 20:07:03 GMT

Hello,

I have a text string variable tha reads:

"PLEASE LOAD TABLE 1394, SAME RATE AS TABLE 276."

Like this variable, there are other instances of unstructured data in this field.

My working thought is to create keywords for the column to extract from the text.

In this example, TABLE is the keyword. I would like to extract "Table 1394" into one variable and "Table 276" into another.

I've worked with Index, Scan, and find with varied results, but I am finding myself getting further away from my desired result. 😞

if find(lowcase(txtstring),'table') >0 then table='table';
do i=1 to countw(txtstring," ");
if scan(lowcase(txtstring),i," ")=table then do;
after=strip(scan(txtstring,i+1," "));
end;
end;
output; end;

this produces only the first iteration of table:

table after
table 276

Any guidance is greatly appreciated.

Re: How do I extract a keyword and subsequent text from a string and...

ChrisNZ — Tue, 29 Sep 2020 01:49:29 GMT

Like this?

data HAVE;
  TEXT="PLEASE LOAD TABLE 1394, SAME RATE AS TABLE 276.  "; output;
  TEXT="PLEASE LOAD TABLE 139, SAME table 2  AS TABLE 27."; output;
data WANT;
  set HAVE;
  if _N_=1 then REGEX+prxparse('/(TABLE \d*)/i');
  START = 1;
  STOP  = length(TEXT);
  call prxnext(REGEX, START, STOP, TEXT, POS, LEN);
  do while (POS);
    STR   = substr(TEXT,POS,LEN);
    output;
    call prxnext(REGEX, START, STOP, TEXT, POS, LEN);
  end;
  keep STR;
run;
proc print data=WANT;
run;

Obs	STR
1	TABLE 1394
2	TABLE 276
3	TABLE 139
4	table 2
5	TABLE 27

Re: How do I extract a keyword and subsequent text from a string and...

Ksmit — Tue, 29 Sep 2020 12:47:40 GMT

Much appreciated Chris. This worked like a charm.

I am thinking about putting this in a do loop for as many keywords as I have (presently 6) this way if any of the words populate in each iteration it will create a counter column per keyword.

Thanks for starting me off.

Re: How do I extract a keyword and subsequent text from a string and...

ChrisNZ — Wed, 30 Sep 2020 00:23:34 GMT

You needn't do a DO loop.

If say you want to also capture CHAIR 99, you can use:

REGEX+prxparse('/(TABLE \d*|CHAIR \d*)/i');

Re: How do I extract a keyword and subsequent text from a string and...

Ksmit — Wed, 30 Sep 2020 12:55:36 GMT

EVEN Better! Thanks for this follow up as it got me exactly what I needed.