Problems with datetime conversion

[Chi]

I am having problems with merging datasets with different formats of date& time. :smileyplain:

Most of the datasets' date&time data look like 2009/4/1 17:00 (character type $14. in one column), but two datasets' date&time values look like

01JAN10:17:00:00 (DATETIME, numeric type).

Is there a way that I can convert the datetime data from 01JAN10:17:00:00 (DATETIME, numeric type) to  2009/4/1 17:00 (character type $14.)?

Thanks,

Chin

[Tom · Accepted solution]

It would be better to convert the text string to an actual datetime value if you want to be able to use them for merging.

Let's assume that your character variable that looks like a datetime value is named DATETIMEC and you want to create a new variable named DATETIME.

data want;

  set have;

  datetime=input(datetimec,anydtdtm.);

  format datetime datetime.

run;

Note that $14 is too short to store all possible values in YYYY/MM/DD HH:MM format. That would require 16 characters.

Note also that if you store datetimes as character strings without the leading zeros for month,day and hour then it will not sort in chronological order.

But given those problems if you did want to convert an actual datetime value to that format you could do it using PUT functions.

data want;

  set have;

   length datetimec $16;

  datetimec=put(datepart(datetime),yymmdds10.)||' '||put(timepart(datetime),time5.);

run;

[Ksharp]

proc format;
 picture fmt
  other='%Y/%m/%d %0H:%0M' (datatype=datetime);
run;
data _null_;
 a='01JAN10:17:00:00'dt;
 b='01APR09:17:00:00'dt;
 want=put(a-intck('dtseconds',b,a),fmt21.);
 put want=;
run;

Ksharp