Extract Datetime from Character String

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Extract Datetime from Character String

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!


Accepted Solutions
Solution
‎12-13-2017 03:05 PM
PROC Star
Posts: 831

Re: Extract Datetime from Character String

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;

View solution in original post


All Replies
Super User
Posts: 8,590

Re: Extract Datetime from Character String

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Extract Datetime from Character String

Posted in reply to KurtBremser
Yes! "Date:" would be it.
Solution
‎12-13-2017 03:05 PM
PROC Star
Posts: 831

Re: Extract Datetime from Character String

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;

Occasional Contributor
Posts: 6

Re: Extract Datetime from Character String

Posted in reply to novinosrin
Thank you! This is exactly what I needed.
Occasional Contributor
Posts: 10

Re: Extract Datetime from Character String

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;
Occasional Contributor
Posts: 6

Re: Extract Datetime from Character String

Thank you! This worked great.
PROC Star
Posts: 442

Re: Extract Datetime from Character String

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;
Super User
Posts: 12,148

Re: Extract Datetime from Character String

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.

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 237 views
  • 6 likes
  • 6 in conversation