date time format

[Xiaoningdemao]

Hello all,

I encountered a time format: dd/mm/yy hhmm, like: 16/01/15 1030.

The format for it now is character $15. I want to put it to numerical. I tried:

DT_TM = input(DateTime, anydtdtm40.);

but it doesn't work, is anyone know what format this is? and how to put it to numerical value?

Thank you!!

Best wishes.

[PGStats]

No informat will read these datetime values directly. You can either

1) Read them as two separate fields and assemble them with DHMS()

2) Extract the two fields, convert them to date and time and assemble them with dhms()

3) Add a colon in the time field and read with informat anydtdtm. 

Options 2 and 3 are illustrated in:

data _null_;
DateTime = "16/01/15 1030";
dt = input(scan(DateTime,1," "), ddmmyy8.);
tm = input(scan(DateTime,2," "), hhmmss4.);
DT_TM1 = dhms(dt, hour(tm), minute(tm), second(tm));
DT_TM2 = input(prxChange("s#(\s*\d\d/\d\d/\d\d\s+\d\d)(.+)#\1:\2#o",1,DateTime), anydtdtm.);
format DT_TM1 DT_TM2 datetime17.;
put _all_;
run;
 

[Xiaoningdemao]

Dear PG,

Thank you for your solution! I accept RahulG's because her/his solution fits my data structure better.

Thank you all the same~

Best wishes.

[RahulG · Accepted solution]

data have;
input dt_val $15. ;
datalines;
16/01/15 1030
06/01/15 1230
;

data want;
format dt datetime20.;
set have;
dt=dhms(input(substr(dt_val,1,8),ddmmyy8.),input(substr(dt_val,10,2),$2.),input(substr(dt_val,12,2),$2.),0);
run;

It is not the best solution, you can use until someone suggest the correct informat to read.

[Xiaoningdemao]

Dear RahulG,

Thank you! I think this is what I'm looking for!

Best wishes,

Yuan

[ballardw]

A big part of the issue is that values like

16/01/15

Could be 16 January 2015 (or 1915) or 15 January 2016 (or 1916). And if you get down to something like 06/01/15 you get to consider the possibility of 01 June 2015 (1915).

Personally I think anyone, yes including the US government, should be flogged with wet spaghetti noodles for using two-digit years in this day and age.

So the SAS format guessers will give up on some values. And it is very likely the 06/01/15 gets the June interpretation which is very bad if it should be the January 2016.

Also note that there is an option in SAS on how to treat two-digit years with the YEARCUTOFF option. The current default is 1920 which means year 20 is 1920. See any potential problem popping up in a couple years? Projection dates into 2020 will get treated as 1920 if they are read into SAS with two-digit years and throw the heck off models in SAS for a bit.