topic Re: Character to Date and Time separately in SAS Programming

Character to Date and Time separately

pacman94 — Tue, 12 Jan 2021 15:12:31 GMT

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

Re: Character to Date and Time separately

PeterClemmensen — Tue, 12 Jan 2021 15:32:24 GMT

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

Re: Character to Date and Time separately

Kurt_Bremser — Tue, 12 Jan 2021 15:29:51 GMT

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;

Re: Character to Date and Time separately

ballardw — Tue, 12 Jan 2021 15:47:32 GMT

@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

Re: Character to Date and Time separately

Shmuel — Tue, 12 Jan 2021 15:53:22 GMT

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;

Re: Character to Date and Time separately

Tom — Tue, 12 Jan 2021 15:59:35 GMT

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