Help using Base SAS procedures

Problems with datetime conversion

Accepted Solution Solved
Reply
Occasional Contributor Chi
Occasional Contributor
Posts: 5
Accepted Solution

Problems with datetime conversion

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


Accepted Solutions
Solution
‎09-25-2012 11:57 PM
Super User
Super User
Posts: 6,500

Re: Problems with datetime conversion

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;

View solution in original post


All Replies
Solution
‎09-25-2012 11:57 PM
Super User
Super User
Posts: 6,500

Re: Problems with datetime conversion

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;

Super User
Posts: 9,681

Re: Problems with datetime conversion

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

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 299 views
  • 4 likes
  • 3 in conversation