DATA Step, Macro, Functions and more

How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

Reply
Occasional Contributor
Posts: 11

How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

How to convert YYYY-MM-DDTHH:MM format to datetime16. format ?

 

I have below data where some of them doesn't have timepart. I need to convert this data to datetime16. format

 

2012-07-26

2012-07-26T15:41

2012-07-26T15:55

2012-08-08

 

 

Can someone please teach me ?

 

 

Trusted Advisor
Posts: 1,117

Re: How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

[ Edited ]

Hi @bobbyc,

 

Try this:

data test;
input;
dt=input(translate(_infile_,':','T'), anydtdtm.);
format dt datetime16.;
cards;
2012-07-26
2012-07-26T15:41
2012-07-26T15:55
2012-08-08
;

This creates a SAS datetime value which you can format with DATETIME16. (as shown above) or any other suitable format. Missing time parts are imputed as 00:00:00.

 

Edit: If your data are already in a SAS dataset, say olddt='2012-07-26T15:41', you can transform them similarly:

dt=input(translate(olddt,':','T'), anydtdtm.);
Frequent Contributor
Posts: 83

Re: How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

Posted in reply to FreelanceReinhard

Reinhard,

 

  I like to have control over my conversions, so I test the values:

 

data dt ;
  length dtc $ 30
         dtf $ 3
         ;
  input dtc ;

  if prxmatch( "/^\d{4,4}-\d{2,2}-\d{2,2}$/" , strip( dtc ))
  then
    do ;
       dt = input( cats( dtc , "T00:00:00" ) , e8601dt. ) ;
       dtf = "HMS" ;
    end ;
  else if prxmatch( "/^\d{4,4}-\d{2,2}-\d{2,2}T\d{2,2}:\d{2,2}$/" , strip( dtc ))
  then
    do ;
       dt = input( cats( dtc , ":00" ) , e8601dt. ) ;
       dtf = "S" ;
    end ;

datalines ;
2012-07-26
2012-07-26T15:41
2012-07-26T15:55
2012-08-08
run ;

 

proc print data = dt ;
   format dt datetime16. ;
run ;

 

A few points: it is worth emphasizing that SAS datetimes variables are just a number.  How you present that number is controlled by the format.  The datetime is at least as refined as seconds, so all of these values are truncated.  I include a flag to indicate the level of imputation (I choose "00", but you could also use any value to "59", but SAS will round for the DATETIME16. format).  Some standards might represent "HMS" as "H", because if you imputated hours, then minutes and seconds were missing, too (presumably).  Lastly, the regular expressions are too broad, but serve for some data.

 

HTH,

 

Kevin

Frequent Contributor
Posts: 83

Re: How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

Posted in reply to KevinViel

Sorry, I should have addressed the OC, bobbyc, not Rienhard.

 

-Kevin

Super User
Super User
Posts: 7,060

Re: How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

Use the informat of B8601DT. 

 

Frequent Contributor
Posts: 83

Re: How to convert YYYY-MM-DDTHH:MM to datetime16. format ?

Nice!  Great improvement.

 

Thanks,

 

Kevin

Ask a Question
Discussion stats
  • 5 replies
  • 970 views
  • 2 likes
  • 4 in conversation