Convert Datetime Character to Date Format with Varying Character Lengths

[mariko5797]

I want to convert dates stored as character to actual date format. However, one of the date columns is inconsistent. I tried the macro shown below; however, it isn't converting anything that contains a time. Is there another informat I should be using instead?

Code:

data have;
 input id $ seq char_date : $20.;
 cards;
 	1	1	2018-04-25T11:26
	1	2	2018-04-25
	1	3	2018-04-26
	2	1	2017-09-30T06:23
	2	2	2017-10-05
	3	1	2018-03-17
	3	2	2018-03-17
	3	3	2018-04-02T22:00
	4	1	2017-12-04T13:41
	4	2	2017-12-08T09:20
	5	1	2018-01-15
	;
run;

%macro dtfmt(dsn, var);
	data &dsn.;
	 set &dsn.;

	 &var.c = input(&var., anydtdte22.);
	run;
%mend dtfmt;

%dtfmt(dsn= have, var= char_date);

Result:

Desired Result:

data want;
 input id $ seq date : yymmdd10. time : time6.;
 format date date9. time time6.;
 cards;
 	1	1	2018-04-25	11:26
	1	2	2018-04-25	.
	1	3	2018-04-26	.
	2	1	2017-09-30	06:23
	2	2	2017-10-05	.
	3	1	2018-03-17	.
	3	2	2018-03-17	.
	3	3	2018-04-02	22:00
	4	1	2017-12-04	13:41
	4	2	2017-12-08	09:20
	5	1	2018-01-15	.
	;
run;

[ballardw]

IF you want a date from that structure use the informat YYMMDD10.

That will only read the 10 characters you have an ignore the rest.

read the remainder as time with

Time = input (scan(char_date,2,'T'),time.);

Suggestion: Apply a format so you can tell the value instead of seeing numbers like 21299.

Warning: habitual use of the

data somename;
   set somename;
  <code>
run;

will at some time cause you a headache because of minor logic errors. That coding completely replaces the original data set and you may have a lot of fun tracing back just how you managed to destroy the values you need.

---

@mariko5797 wrote:

I want to convert dates stored as character to actual date format. However, one of the date columns is inconsistent. I tried the macro shown below; however, it isn't converting anything that contains a time. Is there another informat I should be using instead?

Code:

data have;
 input id $ seq char_date : $20.;
 cards;
 	1	1	2018-04-25T11:26
	1	2	2018-04-25
	1	3	2018-04-26
	2	1	2017-09-30T06:23
	2	2	2017-10-05
	3	1	2018-03-17
	3	2	2018-03-17
	3	3	2018-04-02T22:00
	4	1	2017-12-04T13:41
	4	2	2017-12-08T09:20
	5	1	2018-01-15
	;
run;

%macro dtfmt(dsn, var);
	data &dsn.;
	 set &dsn.;

	 &var.c = input(&var., anydtdte22.);
	run;
%mend dtfmt;

%dtfmt(dsn= have, var= char_date);

Result:

Desired Result:

data want;
 input id $ seq date : yymmdd10. time : time6.;
 format date date9. time time6.;
 cards;
 	1	1	2018-04-25	11:26
	1	2	2018-04-25	.
	1	3	2018-04-26	.
	2	1	2017-09-30	06:23
	2	2	2017-10-05	.
	3	1	2018-03-17	.
	3	2	2018-03-17	.
	3	3	2018-04-02	22:00
	4	1	2017-12-04	13:41
	4	2	2017-12-08	09:20
	5	1	2018-01-15	.
	;
run;

---

[mariko5797]

Is there a way to extract only the time from CHAR_DATE? I want both the date and time parts.

[Tom · Accepted solution]

What value do you want for the time of day when it is not specified?  Is it ok to set it to zero (midnight)?

data have;
   input id $ seq char_date $20.;
cards;
1	1	2018-04-25T11:26
1	2	2018-04-25
1	3	2018-04-26
2	1	2017-09-30T06:23
2	2	2017-10-05
3	1	2018-03-17
3	2	2018-03-17
3	3	2018-04-02T22:00
4	1	2017-12-04T13:41
4	2	2017-12-08T09:20
5	1	2018-01-15
;

data want;
  set have;
  dt = input(char_date,b8601dt20.);
  date=datepart(dt);
  time=timepart(dt);
  format dt datetime19. date date9. time tod8.;
run;
proc print;
run;

Results

Obs    id    seq    char_date                            dt         date        time

  1    1      1     2018-04-25T11:26     25APR2018:11:26:00    25APR2018    11:26:00
  2    1      2     2018-04-25           25APR2018:00:00:00    25APR2018    00:00:00
  3    1      3     2018-04-26           26APR2018:00:00:00    26APR2018    00:00:00
  4    2      1     2017-09-30T06:23     30SEP2017:06:23:00    30SEP2017    06:23:00
  5    2      2     2017-10-05           05OCT2017:00:00:00    05OCT2017    00:00:00
  6    3      1     2018-03-17           17MAR2018:00:00:00    17MAR2018    00:00:00
  7    3      2     2018-03-17           17MAR2018:00:00:00    17MAR2018    00:00:00
  8    3      3     2018-04-02T22:00     02APR2018:22:00:00    02APR2018    22:00:00
  9    4      1     2017-12-04T13:41     04DEC2017:13:41:00    04DEC2017    13:41:00
 10    4      2     2017-12-08T09:20     08DEC2017:09:20:00    08DEC2017    09:20:00
 11    5      1     2018-01-15           15JAN2018:00:00:00    15JAN2018    00:00:00