topic How to extract a date from a text in SAS Programming

How to extract a date from a text

knveraraju91 — Thu, 17 Aug 2017 23:00:39 GMT

Dear,

I need to extract date from a text. Please suggest. Thank you.

data one;

input text $;

datalines;

Today is friday (08aug2017) in Newyork

Today (07Jun2017) is Monday in Texas

;

output needed;

date

2017-08-08

2017-06-07

Re: How to extract a date from a text

art297 — Fri, 18 Aug 2017 00:36:04 GMT

If year is the only four digit number in the text field, then you could get away with something as simple as:

data one;
  infile datalines truncover;
  format date yymmdd10.;
  input text $255.;
  date=input(substr(text,prxmatch('/\d\d\d\d/', text)-5,9),date9.);
  datalines;
123456789012345678901234
Today is friday (08aug2017) in Newyork
Today (07Jun2017) is Monday in Texas
;

Art, CEO, AnalystFinder.com

Re: How to extract a date from a text

Reeza — Fri, 18 Aug 2017 01:12:24 GMT

Is it always in brackets? For your example you could use SCAN but it depends on how representative your sample is in comparison to your actual data.

Re: How to extract a date from a text

PGStats — Fri, 18 Aug 2017 03:14:46 GMT

If the format is always ddmmmyyyy, try:

data one;
input text $64.;
datalines;
Today is friday (08aug2017) in Newyork
Today (07Jun2017) is Monday in Texas
;

data two;
set one;
dPos = prxmatch ("/\d\d(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[12]\d\d\d/io",
    text); 
if dPos > 0 then date = input(substr(text,dPos,9),date9.);
format date yymmdd10.;
drop dPos;
run;

proc print noobs; run;

Re: How to extract a date from a text

ChrisNZ — Fri, 18 Aug 2017 04:38:01 GMT

You can even write your own pattern-searching informat.

proc format;

 invalue finddate (default=50)

 's/.*?             (?# lazy match of any character      )
    (               (?# 1st capture group                )
     \d{2}          (?# 2 digits [day]                   )
     \w{3}          (?# 3 letters [month]                )
     \d{4}          (?# 4 digits [year]                  )
    )               (?# close 1st capture group          )
    .*?             (?# lazy match of any character      )  
    /\1/x'         %*  keep date. type text   ;
                                               (regexpe)= [date9.] 

 other                                                  = .;

run;     
        
data _null_; 
  input X finddate.;
  putlog 'Date= ' X date9.  ; 
cards;
Today is friday (08aug2017) in Newyork
Today (07Jun2017) is Monday in Texas
run;

Date= 08AUG2017
Date= 07JUN2017