topic Re: Extract Datetime from Character String in SAS Programming

Extract Datetime from Character String

mglogan — Wed, 13 Dec 2017 18:14:36 GMT

Hello everyone,

I need to extract datetime from a character string; I am not sure how to go about it.

For example the field for the variable "comment" is:

"Scheduled Appt. Study: Healthy Brain Study. Visit: Consultation. Date: 11/30/2017 11:00 AM"

Is there a function to keep only datetime for all observations?

Thank you!

Re: Extract Datetime from Character String

Kurt_Bremser — Wed, 13 Dec 2017 18:17:56 GMT

Do you have a reliable "marker", like the "Date:" string?

Re: Extract Datetime from Character String

mglogan — Wed, 13 Dec 2017 18:21:15 GMT

Yes! "Date:" would be it.

Re: Extract Datetime from Character String

novinosrin — Wed, 13 Dec 2017 18:28:02 GMT

Are you after this:

or please correct my understanding

data have;
input var $100.;
datalines;
Scheduled Appt. Study: Healthy Brain Study. Visit: Consultation. Date: 11/30/2017 11:00 AM
;

data want;
set have;
just_time=substr(var,index(var,'Date')+5);
run;

Re: Extract Datetime from Character String

lopezr — Wed, 13 Dec 2017 18:54:04 GMT

With a slight modification of @novinosrin's code you can create a numeric date/time variable.

data want;
  set have;
  just_time=input(substr(var,index(var,'Date')+5), mdyampm25.);
  format just_time mdyampm25.;
run;

Re: Extract Datetime from Character String

kiranv_ — Wed, 13 Dec 2017 19:13:17 GMT

Just another way. it works only when your date time is in pattern you have shown

data have;
input comment $150.;
datalines;
Scheduled Appt. Study: Healthy Brain Study. Visit: Consultation. Date: 11/30/2017 11:00 AM
Scheduled Appt. Study: Healthy Brain Study. Visit: Consultation. Date: 11/30/2017 12:00 PM looks good
;


data want;
   set have;
   name = prxchange('s/(.*?)(\d{1,2}\/\d{2}\/\d{4} \d{2}:\d{2}\s[A-Za-z]{2})(.*)/$2/', -1, comment);
run;

Re: Extract Datetime from Character String

mglogan — Wed, 13 Dec 2017 20:05:56 GMT

Thank you! This worked great.

Re: Extract Datetime from Character String

mglogan — Wed, 13 Dec 2017 20:06:21 GMT

Thank you! This is exactly what I needed.

Re: Extract Datetime from Character String

ballardw — Wed, 13 Dec 2017 21:31:53 GMT

If you are reading this from a text file you might find it easier just to modify the original code reading it:

data have;
   informat ddate mmddyy10.;
   format   ddate mmddyy10.;
   input  @"Date: " Ddate;
datalines;
Scheduled Appt. Study: Healthy Brain Study. Visit: Consultation. Date: 11/30/2017 11:00 AM
;
run;

The @ used in this manner in an input statement finds the sting in quotes and starts inputting at that point. If this is part of a longer input statement you may need to resent the current column position by @1 @"Date: " to avoid attempting to read past the end of the line.