topic Re: Extract date from a string in SAS Programming

Extract date from a string

Citrine10 — Tue, 20 Sep 2022 07:45:50 GMT

Hi there,

I have a column containing really long test and I am struggling to extract the date portion in the string. The date format is yyyymmdd.

An example of the string:

Schoolrecords_class1B_20190809_2209.doc

The character strings vary and I therefore cannot specify a position of the string.

Re: Extract date from a string

PeterClemmensen — Tue, 20 Sep 2022 08:11:22 GMT

But the format is always 8 digits, correct?

Re: Extract date from a string

Citrine10 — Tue, 20 Sep 2022 08:12:00 GMT

correct

Re: Extract date from a string

PeterClemmensen — Tue, 20 Sep 2022 08:15:30 GMT

Ok. Try this

data want;
   string = 'Schoolrecords_class1B_20190809_2209.doc';
   c      = prxchange('s/.*_(\d{8})_.*/$1/', -1, string);
   date   = input(c, yymmdd8.);
   format date yymmdd10.;
run;

Re: Extract date from a string

Aku — Tue, 20 Sep 2022 08:16:34 GMT

With scan function you can pick third "word" from original string.

data want;
set have;
Attrib Datevar format=yymmdd10.;
dateVar = input(Scan(DocName,3,'_'),yymmdd10.);
run;

Re: Extract date from a string

Citrine10 — Tue, 20 Sep 2022 08:28:18 GMT

This works for some records but not for a record like this:

data want;
string = 'TST_NoRecs_BBBGRADE12BEFE20210701_220019.doc';
c = prxchange('s/.*_(\d{8})_.*/$1/', -1, string);
date = input(c, yymmdd8.);
format date yymmdd10.;
run;

Re: Extract date from a string

PeterClemmensen — Tue, 20 Sep 2022 08:32:06 GMT

Small change

data want;
   string = 'TST_NoRecs_BBBGRADE12BEFE20210701_220019.doc';
   c      = prxchange('s/.*(\d{8}).*/$1/', -1, string);
   date   = input(c, yymmdd8.);
   format date yymmdd10.;
run;

Re: Extract date from a string

Citrine10 — Tue, 20 Sep 2022 09:01:15 GMT

I think we are almost there. Its just a few odd ones. I thought it was the space and used compress however that didn't work:

data want;
string = "TST_NoRecs_ BBBGRADE22010508_24-T123456789.doc";
c = compress(prxchange('s/.*(\d{8}).*/$1/', -1, string));
date = input(c, yymmdd8.);
format date yymmdd10.;
run;

Re: Extract date from a string

PeterClemmensen — Tue, 20 Sep 2022 09:49:09 GMT

Changed the regular expression, so it doesn't catch 12345678 etc.

data want;
   string = 'TST_NoRecs_ BBBGRADE22010508_24-T123456789.doc';
   c      = prxchange('s/.*([12]\d{3}[01]\d[0123]\d).*/$1/', -1, string);
   date   = input(c, yymmdd8.);
   format date yymmdd10.;
run;

Re: Extract date from a string

Citrine10 — Tue, 20 Sep 2022 10:08:42 GMT

Thank you so much, This worked. Appreciate your help.

One last thing, how do I do it in the case where I only want the date after the DDD
GR1_ClassGr_ DDD1DDD1245620210104_23?
e.g. I would need the 2021-01-04 from the above
This is another dataset where the date needs to be extracted.

Re: Extract date from a string

PeterClemmensen — Tue, 20 Sep 2022 10:14:07 GMT

Glad to help 🙂

Can you describe the exact logic of the string / date you want to extract?

Re: Extract date from a string

Citrine10 — Tue, 20 Sep 2022 10:42:43 GMT

for example if the string is: GR1_ClassGr_ DDD1DDD1245620210104_23?
e.g. I would need the date: 2021-01-04

Re: Extract date from a string

Ksharp — Tue, 20 Sep 2022 11:32:04 GMT

data have;
input have $80.;
pid=prxparse('/\d{8}/');
call prxsubstr(pid,have,p,l);
if p then want=substr(have,p,l);
drop pid p l;
cards;
Schoolrecords_class1B_20190809_2209.doc
;