DATA Step, Macro, Functions and more

How to extract a date from a text

Reply
Super Contributor
Posts: 307

How to extract a date from a text

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

PROC Star
Posts: 8,112

Re: How to extract a date from a text

Posted in reply to knveraraju91

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

 

Super User
Posts: 22,845

Re: How to extract a date from a text

Posted in reply to knveraraju91

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.

Esteemed Advisor
Posts: 5,398

Re: How to extract a date from a text

Posted in reply to knveraraju91

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; 
PG
PROC Star
Posts: 2,226

Re: How to extract a date from a text

Posted in reply to knveraraju91

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

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 298 views
  • 7 likes
  • 5 in conversation