I have the following dataset, I would like to convert date and time that are currently in the CHAR format to date and time respectively, and datetime
have:
RecordID | Date | Time |
1 | 12/14/2020 | 1214 |
2 | 11/19/2019 | 900 |
3 | 1/11/2021 | 815 |
4 | 1/8/2021 | 1314 |
want:
RecordID | Date | Time | date_new | time_new | date_time_new |
1 | 12/14/2020 | 1214 | 12/14/2020 | 12:14 | 12/14/2020 12:14 |
2 | 11/19/2019 | 900 | 11/19/2019 | 9:00 | 11/19/2019 9:00 |
3 | 1/11/2021 | 815 | 1/11/2021 | 8:15 | 1/11/2021 8:15 |
4 | 1/8/2021 | 1314 | 1/8/2021 | 13:14 | 1/8/2021 13:14 |
I tried the following:
data want; set have;
format Date_newmmddyy10. Time_new time10. date_time_new datetime.;
Date_new= input(Date,yymmdd10.);
Time_new= input(substr(Time, 12, 10), time10.);
Date_time_new = DHMS (Date_new, 0, 0, time_new);
run;
Date_new works but not the time_new or date_time_new
Try this
data have;
input Date $ 1-10 Time $ 12-16;
datalines;
12/14/2020 1214
11/19/2019 900
1/11/2021 815
1/8/2021 1314
;
data want;
set have;
date_new = input(Date, mmddyy10.);
time_new = input(cats(substr(Time, 1, length(Time)-2), ":", substr(Time, length(Time)-1)), time5.);
datetime_new = dhms(date_new, 0, 0, time_new);
format date_new mmddyy10. time_new time5. datetime_new datetime20.;
run;
Result:
Date Time date_new time_new datetime_new 12/14/2020 1214 12/14/2020 12:14 14DEC2020:12:14:00 11/19/2019 900 11/19/2019 9:00 19NOV2019:09:00:00 1/11/2021 815 01/11/2021 8:15 11JAN2021:08:15:00 1/8/2021 1314 01/08/2021 13:14 08JAN2021:13:14:00
I seriously doubt that your code worked with the data as presented, as the code uses a YMD order for the date, but the string dates are in MDY.
See this:
data have;
input RecordID $ Date :$10. Time :$4.;
datalines;
1 12/14/2020 1214
2 11/19/2019 900
3 1/11/2021 815
4 1/8/2021 131
;
data want;
set have;
date_new = input(date,mmddyy10.);
if length(time) = 3
then time_new = input(substr(time,1,1)!!":"!!substr(time,2),time5.);
else time_new = input(substr(time,1,2)!!":"!!substr(time,3),time5.);
datetime_new = dhms(date_new,0,0,time_new);
format
date_new yymmdd10.
time_new time5.
datetime_new e8601dt19.
;
run;
@pacman94 wrote:
I have the following dataset, I would like to convert date and time that are currently in the CHAR format to date and time respectively, and datetime
have:
RecordID Date Time 1 12/14/2020 1214 2 11/19/2019 900 3 1/11/2021 815 4 1/8/2021 1314
want:
RecordID Date Time date_new time_new date_time_new 1 12/14/2020 1214 12/14/2020 12:14 12/14/2020 12:14 2 11/19/2019 900 11/19/2019 9:00 11/19/2019 9:00 3 1/11/2021 815 1/11/2021 8:15 1/11/2021 8:15 4 1/8/2021 1314 1/8/2021 13:14 1/8/2021 13:14
I tried the following:
data want; set have;
format Date_newmmddyy10. Time_new time10. date_time_new datetime.;
^missing a space
Date_new= input(Date,yymmdd10.); <wrong informat for the shown example date values
Time_new= input(substr(Time, 12, 10), time10.); <Time has 4 characters from your example, substr is told to start at position 12 and read 10. So either this makes no sense or you have not shown us what your actual "data" looks like.
Date_time_new = DHMS (Date_new, 0, 0, time_new);
run;
Date_new works but not the time_new or date_time_new
data have;
length date $10 time $4;
infile cards dlm='09'x truncover;
input record_id $ date time $;
cards;
1 12/14/2020 1214
2 11/19/2019 900
3 1/11/2021 815
4 1/8/2021 1314
; run;
data want;
set have;
date_new = input(date, mmddyy10.);
time = put(input(time,best4.),z4.);
hh = input(substr(time,1,2),2.); /*hour */
mm = input(substr(time,3,2),2.); /*minute*/
time_new = hms(hh,mm,0);
date_time_new = dhms(date_new,hh,mm,0);
format date_new mmddyy10.
time_new time5.
date_time_new datetime20.;
drop hh mm;
run;
Append two extra zeros to the time string values and then use HHMMSS informat.
data have;
input date_str :$10. time_str :$4. ;
datalines;
12/14/2020 1214
11/19/2019 900
1/11/2021 815
1/8/2021 1314
;
data want;
set have;
date=input(date_str,mmddyy10.);
time=input(cats(time_str,'00'),hhmmss6.);
datetime=dhms(date,0,0,time);
format date date9. time time5. datetime datetime19.;
run;
Obs date_str time_str date time datetime 1 12/14/2020 1214 14DEC2020 12:14 14DEC2020:12:14:00 2 11/19/2019 900 19NOV2019 9:00 19NOV2019:09:00:00 3 1/11/2021 815 11JAN2021 8:15 11JAN2021:08:15:00 4 1/8/2021 1314 08JAN2021 13:14 08JAN2021:13:14: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.