topic Re: Extract Dates from text string & change to date foramt in SAS Procedures

Extract Dates from text string & change to date foramt

Anna_Guo — Sat, 02 Feb 2013 17:08:08 GMT

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.

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

Reeza — Sat, 02 Feb 2013 19:02:41 GMT

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.

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

Anna_Guo — Sat, 02 Feb 2013 20:15:00 GMT

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

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

art297 — Sat, 02 Feb 2013 20:23:52 GMT

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: prx:);

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;

run;

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

Anna_Guo — Sat, 02 Feb 2013 20:34:02 GMT

Arthur,

Thank you very much. It worked.