Help using Base SAS procedures

Extract Dates from text string & change to date foramt

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Extract Dates from text string & change to date foramt


I want to extract dates from a text string, most of them are at the same place,  But some have two dates  and may be in different places.   I can  use substrn function to get most of the dates. Any one has better way to do it? In addition, after substrn, they are in text format, how can I change to the date format still keeping the correct date value?

I attached the simplified sas file, my actual file imuch larger.

Thanks in advance.

Attachment

Accepted Solutions
Solution
‎02-02-2013 03:23 PM
PROC Star
Posts: 7,363

Re: Extract Dates from text string & change to date foramt

Anna,

I would use a regular expression.  The following will work and, hopefully, someone with more regex expertise can simplify it:

libname art "c:\art";

%let pattern=\d{1,2}\/\d{1,2}\/\d{2,4};

data want (drop=pos: len: prxSmiley Happy;

  set art.test;

  format date1 date2 mmddyy10.;

  prxid1=prxparse("/&pattern./o");

  prxid2=prxparse("/&pattern..*?(&pattern.)/o");

  if prxmatch(prxid1,INITIAL_CALL_DATA) then do;

    CALL PRXSUBSTR (prxid1, INITIAL_CALL_DATA, position , length);

    date1=input(substr(INITIAL_CALL_DATA, position , length),mmddyy10.);

    if prxmatch(prxid2,INITIAL_CALL_DATA) then do;

      CALL PRXSUBSTR (prxid1, substr(INITIAL_CALL_DATA, position+5),position2,length2);

      date2=input(substr(INITIAL_CALL_DATA, position+4+position2 , length2),mmddyy10.);

    end;

  end;

run;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Extract Dates from text string & change to date foramt

It looks like you have multiple dates but the text is separated by ;.

You could try using the scan function instead to get the date parts.

I'd use two nested loops, one that first scans for ; and the find/index with substr to find the date parts. 

Contributor
Posts: 36

Re: Extract Dates from text string & change to date foramt

Reeza, Thank you. Can you be more specific? I cannot figure out.

Solution
‎02-02-2013 03:23 PM
PROC Star
Posts: 7,363

Re: Extract Dates from text string & change to date foramt

Anna,

I would use a regular expression.  The following will work and, hopefully, someone with more regex expertise can simplify it:

libname art "c:\art";

%let pattern=\d{1,2}\/\d{1,2}\/\d{2,4};

data want (drop=pos: len: prxSmiley Happy;

  set art.test;

  format date1 date2 mmddyy10.;

  prxid1=prxparse("/&pattern./o");

  prxid2=prxparse("/&pattern..*?(&pattern.)/o");

  if prxmatch(prxid1,INITIAL_CALL_DATA) then do;

    CALL PRXSUBSTR (prxid1, INITIAL_CALL_DATA, position , length);

    date1=input(substr(INITIAL_CALL_DATA, position , length),mmddyy10.);

    if prxmatch(prxid2,INITIAL_CALL_DATA) then do;

      CALL PRXSUBSTR (prxid1, substr(INITIAL_CALL_DATA, position+5),position2,length2);

      date2=input(substr(INITIAL_CALL_DATA, position+4+position2 , length2),mmddyy10.);

    end;

  end;

run;

Contributor
Posts: 36

Re: Extract Dates from text string & change to date foramt

Arthur,

Thank you very much. It worked.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 368 views
  • 3 likes
  • 3 in conversation