DATA Step, Macro, Functions and more

date time format

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

date time format

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.

 


Accepted Solutions
Solution
‎09-02-2016 12:59 PM
Super Contributor
Posts: 266

Re: date time format

Posted in reply to Xiaoningdemao
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.

View solution in original post


All Replies
Respected Advisor
Posts: 4,920

Re: date time format

Posted in reply to Xiaoningdemao

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;
 
PG
Frequent Contributor
Posts: 76

Re: date time format

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.

Solution
‎09-02-2016 12:59 PM
Super Contributor
Posts: 266

Re: date time format

Posted in reply to Xiaoningdemao
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.

Frequent Contributor
Posts: 76

Re: date time format

Dear RahulG,

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

Best wishes,

Yuan
Super User
Posts: 11,343

Re: date time format

Posted in reply to Xiaoningdemao

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 335 views
  • 4 likes
  • 4 in conversation