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;
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
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;
Is there a way to extract only the time from CHAR_DATE? I want both the date and time parts.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.