topic Re: How to extract a number in a specific format out of a string in SAS Programming

How to extract a number in a specific format out of a string

PrudhviB — Tue, 24 Mar 2020 02:27:12 GMT

Hi SAS Experts i have below string and i am trying to extract the number that is in the following highlighted format, the issue is the format is not consistent but i would like to extract anything after "reference # " just till the numbers as shown below.

string:

“Send email to reference, Mailbox..* Email Subject Line - reference Follow-Up....Body of email (complete template below).... ....student Name.... James GENEVIEVE...Student Phone #.... 12345712457 best, 5458712458....reference #.... 012000222.1.... .... ONFIDENTIALITY NOTICE..This communication may contain privileged or confidential information. If you are not the intended recipient or received this communication by error, please notify the sender and delete the message without copying or disclosing it. Thank you..... .... .."

desired output:

"012000221.1"

in advance, thank you for your effort.

Re: How to extract a number in a specific format out of a string

Patrick — Tue, 24 Mar 2020 03:48:59 GMT

A regular expression could help. What exactly will work depends on the details/variations in your actual data.

data have;
  infile datalines truncover;
  length string $2000;
  retain string;
  input;
  string=catx(' ',string,_infile_);
  if _n_=10 then output;
datalines;
Send email to  reference, Mailbox..*        
Email Subject Line - reference Follow-Up....
Body of email (complete template below).... ....
student Name.... James GENEVIEVE...Student Phone #.... 
12345712457 best, 5458712458....reference #.... 012000222.1.... .... 
ONFIDENTIALITY NOTICE..This communication may contain privileged or 
confidential information. If you are not the intended recipient or 
received this communication by error, please notify the sender 
and delete the message without copying or disclosing it. 
Thank you..... .... ..
;

data want;
  set have;
  length want_str $32;
  _prxid=prxparse('/reference #[^#\d]*(\d+\.?\d*)/oi');
  if prxmatch(_prxid,trim(string))>0 then 
    want_str=prxposn(_prxid,1,trim(string));
run;

proc print data=want;
  var want_str;
run;

Re: How to extract a number in a specific format out of a string

ChrisNZ — Tue, 24 Mar 2020 05:55:33 GMT

Depending on the exact requirement, another option:

data WANT;
  LINE="Send email to  reference, Mailbox..*        Email Subject Line - reference Follow-Up....Body of email (complete template below).... ....student Name.... James GENEVIEVE...Student Phone #.... 12345712457 best, 5458712458....reference #.... 012000222.1.... .... ONFIDENTIALITY NOTICE..This communication may contain privileged or confidential information. If you are not the intended recipient or received this communication by error, please notify the sender and delete the message without copying or disclosing it. Thank you..... .... ..";
  STR=prxchange('s/'           %* substitution requested;
              ||'.*'           %* match anything;
              ||'reference #'  %* then reference space hash;
              ||'[^\d]*'       %* then more optional text except digits ;
              ||'(\d+\.?\d*)'  %* then digits including an optional embedded dot <= capture this;
              ||'.*'           %* then the rest of the string;
              ||'/\1'          %* replace all with captured group ;
              ||'/',1,LINE);
  putlog STR=;
run;

STR=012000222.1

Re: How to extract a number in a specific format out of a string

Patrick — Tue, 24 Mar 2020 06:47:54 GMT

@ChrisNZ

Using prxchange() is what I've done first as well but then realized that you'll end up with the source string if there is no match.

Re: How to extract a number in a specific format out of a string

ChrisNZ — Tue, 24 Mar 2020 09:41:59 GMT

@Patrick True

if STR=LINE then STR=' ';

Not saying this is better. Just another option. 🙂

RegEx are expensive, I prefer to use them just once if possible.

Re: How to extract a number in a specific format out of a string

Patrick — Tue, 24 Mar 2020 11:47:42 GMT

Doesn’t prxposn() just retrieve the capture buffer without parsing the source string again?

Re: How to extract a number in a specific format out of a string

PrudhviB — Tue, 24 Mar 2020 20:05:02 GMT

Just Amazing this worked like charm and not only that i learn something new with your detailed explanation @ChrisNZ
if there is a reference page where i can get to learn this please do share.

Re: How to extract a number in a specific format out of a string

ChrisNZ — Wed, 25 Mar 2020 07:30:11 GMT

> Doesn’t prxposn() just retrieve the capture buffer without parsing the source string again?

You are right. Your solution is actually more efficient. I never used to use this function but I'll keep in mind now.

Thanks for the heads up. 🙂

Re: How to extract a number in a specific format out of a string

ChrisNZ — Wed, 25 Mar 2020 07:31:58 GMT

> a reference page

This syntax is called a regular expression and there a tons of didactic resources online. That's how I learnt. 🙂