DATA Step, Macro, Functions and more

How to extract a date from a text

Reply
Super Contributor
Posts: 325

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

Super User
Posts: 8,220

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: 24,025

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,626

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
Super User
Posts: 2,513

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
  • 419 views
  • 7 likes
  • 5 in conversation