topic Re: Extract date from string in SAS Programming

Extract date from string

kaziumair — Tue, 30 Mar 2021 10:09:12 GMT

Hi , I want to extract date from the following string

"<article id="post-13669" class="post-13669 stories type-stories status-publish has-post-thumbnail hentry category-homepage-stories tag-amab tag-amabhungane tag-dewald-van-rensburg tag-factory tag-idc tag-ikra tag-insa tag-investigative-journalism tag-kzn tag-peter-maskell-auctioneers tag-pots tag-turkey-pots" link="https://amabhungane.org/stories/210312-funder-vs-funder-in-r500m-pots-fiasco/">
"

the date is -> 210312

How can I extract it?

Re: Extract date from string

Kurt_Bremser — Tue, 30 Mar 2021 10:27:06 GMT

Brute force attack:

data have;
string = '<article id="post-13669" class="post-13669 stories type-stories status-publish has-post-thumbnail hentry category-homepage-stories tag-amab tag-amabhungane tag-dewald-van-rensburg tag-factory tag-idc tag-ikra tag-insa tag-investigative-journalism tag-kzn tag-peter-maskell-auctioneers tag-pots tag-turkey-pots" link="https://amabhungane.org/stories/210312-funder-vs-funder-in-r500m-pots-fiasco/">';
run;

data want;
set have;
pos = index(string,'link="https:');
substring = scan(substr(string,pos+13),-2,"/");
date = input(substr(substring,1,6),yymmdd6.);
format date yymmdd10.;
run;

Someone will come up with a clever application of PRXMATCH, I'm sure.

Re: Extract date from string

andreas_lds — Tue, 30 Mar 2021 10:28:45 GMT

Most likely by using a regular expression. For the text you have posted the expression

\/(\d{6})\D

seems ok.

See docs of prxmatch, prxparse and prxposn for details.

Re: Extract date from string

PaalNavestad — Tue, 30 Mar 2021 10:29:50 GMT

Hi, one way would be to use regexp to find 6 numbers in a row or maybe /followed by 6 numbers then you have your date. Further I would split the string into year, month day as numbers and use the yymmdd function. You may also try anydtdte. informat.

Re: Extract date from string

Kurt_Bremser — Tue, 30 Mar 2021 10:29:57 GMT

As predicted 😉

Re: Extract date from string

Kurt_Bremser — Tue, 30 Mar 2021 10:35:01 GMT

Don't use the ANY* informats for strings where the date structure is not very clear. With the given string, you might end up with 2012-03-21 or 2021-03-12, depending on locale of the SAS session.

Since you can only use the ANY* informats reliably when the structure is clear already, you never use them, unless you want unpredictable results.

Re: Extract date from string

kaziumair — Tue, 30 Mar 2021 10:41:41 GMT

Hi , Thanks for your suggestion , I will check the docs mentioned

Re: Extract date from string

kaziumair — Tue, 30 Mar 2021 10:42:19 GMT

Thank you for your help .

Re: Extract date from string

Ksharp — Tue, 30 Mar 2021 13:17:26 GMT

data want;
set have;
p=prxmatch('/(?<=\/)\d/',string);
if p then want=substr(string,p,6);
run;