topic Re: Need to extract text between characters which includes quotation marks in SAS Programming

Need to extract text between characters which includes quotation marks

RandoDando — Fri, 15 Mar 2024 17:32:48 GMT

I have a raw data file which is importing some text columns with tags on either side of the data. The tags are not exactly the same on the left side for each case, and include no space. An example would be this:

<a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>

I want to extract from that PROPER DATA.

The alphanumeric portion after href= on the left side can vary from row to row.

I've tried this, but it does nothing.

pos1=findc(StringVar, '>');
pos2=findc(StringVar, '</a>');
NewVar = substr(StringVar, nos1+1, nos2-1-nos2);

I've tried this and had some result. No change.

pos1=findc(StringVar, '_blank">');
pos2=findc(StringVar, '</a>');
NewVar = substr(StringVar, nos1+1, nos2-1-nos2);

How can I extract that text?

Re: Need to extract text between characters which includes quotation marks

Quentin — Fri, 15 Mar 2024 18:28:09 GMT

Assuming the only time the < and > symbols are in your data is for the tags, you could use the SCAN function and treat those as delimiters:

6    data want ;
7      str='<a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>' ;
8      length want $50 ;
9      want=scan(str,2,'<>') ;
10     put want= ;
11   run ;

want=PROPER DATA
NOTE: The data set WORK.WANT has 1 observations and 2 variables.

Re: Need to extract text between characters which includes quotation marks

ballardw — Fri, 15 Mar 2024 18:28:50 GMT

If your example is of all the lines you need to read (unlikely I suspect) then perhaps:

data example;
   string='<a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>';
   want = scan(string,2,'<>');
run;

SCAN will allow you to provide characters that delimit string values. Since your line starts with an < then it precedes the first string which would be everything until the next < or > character.

You would want to assign a length to the target variable, Want above, to hold the longest expected value prior to use.

I would expect strange results. Your use of

nos2-1-nos2)

Would tent to always result in a value of -1. ( nos2-1-nos2 is equivalent to nos2-nos2-1) Which when you get negative lengths SUBSTR tends to odd things as documented in the online help:

Interaction If length is zero, a negative value, or larger than the length of the expression that remains in string after position, SAS extracts the remainder of the expression. SAS also sets _ERROR_ to 1 and prints a note to the log indicating that the length argument is invalid.

Interaction	If length is zero, a negative value, or larger than the length of the expression that remains in string after position, SAS extracts the remainder of the expression. SAS also sets _ERROR_ to 1 and prints a note to the log indicating that the length argument is invalid.

There should be notes in the log.

@RandoDando wrote:

I have a raw data file which is importing some text columns with tags on either side of the data. The tags are not exactly the same on the left side for each case, and include no space. An example would be this:

<a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>

I want to extract from that PROPER DATA.

The alphanumeric portion after href= on the left side can vary from row to row.

I've tried this, but it does nothing.
pos1=findc(StringVar, '>');
pos2=findc(StringVar, '</a>');
NewVar = substr(StringVar, nos1+1, nos2-1-nos2);
I've tried this and had some result. No change.
pos1=findc(StringVar, '_blank">');
pos2=findc(StringVar, '</a>');
NewVar = substr(StringVar, nos1+1, nos2-1-nos2);
How can I extract that text?

Re: Need to extract text between characters which includes quotation marks

RandoDando — Fri, 15 Mar 2024 19:03:56 GMT

This seems to work. THanks

Re: Need to extract text between characters which includes quotation marks

FreelanceReinh — Fri, 15 Mar 2024 19:04:35 GMT

Hello @RandoDando,

Let's create a few more test strings as sample data:

data have;
input StringVar &$100.;
cards;
<a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>
<a href="a213w9245992999Vrh" target="_blank"></a>
<b>bla</b><abc>xyz</abc><a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>
<a href="a213w9245992999Vrh" target="_blank">
TEST</a>
;

Now let's fix your first approach:

data want(drop=pos:);
set have;
pos0=find(StringVar, '<a ');
pos1=findc(StringVar, '>', pos0+3);
pos2=find(StringVar, '</a>', pos1+1);
NewVar = substrn(StringVar, pos1+1, pos2-1-pos1);
run;

To make it more robust (against strings such as 'NOT>THIS</a>'), you may want to add IF conditions like

data want(drop=pos:);
set have;
pos0=find(StringVar, '<a ');
if pos0 then pos1=findc(StringVar, '>', pos0+3);
if pos1 then pos2=find(StringVar, '</a>', pos1+1);
if pos2 then NewVar = substrn(StringVar, pos1+1, pos2-1-pos1);
run;

possibly with nested DO-END blocks if performance is an issue. You could also modify your second approach analogously.

Note that the separation of the fourth and fifth observation prevents 'TEST' from being found.

Re: Need to extract text between characters which includes quotation marks

Ksharp — Mon, 18 Mar 2024 05:11:39 GMT

It is best example/scenario for Perl Regular Expression.

data have;
input StringVar &$100.;
cards;
<a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>
<a href="a213w9245992999Vrh" target="_blank"></a>
<b>bla</b><abc>xyz</abc><a href="a213w9245992999Vrh" target="_blank">PROPER DATA</a>
<a href="a213w9245992999Vrh" target="_blank">
TEST</a>
;
data want;
 set have;
 want=prxchange('s/<.+?>//',-1,StringVar);
run;