How do I convert hh:mm (text) into TIME5.2 with INPUT

[JohanK]

Dear reader,

I have a question regarding converting date and time in a text format (Excel) into a separate date and timefield. I do not want to touch or modify the Excel,  the date and time are a Text field, e.g. 2021-02-13T09:46.

I manage to take out the date part and convert it to a DATE9. format.

But as for the time this does not work. Is there a solution how to best get my results? 

I want SAMPDT_CC as DATE9,  which works

and SAMPTM_CC as TIME5.2 and I cannot manage to get this..

Here is  code (also uploaded)

PROC IMPORT
DATAFILE="C:\.......\SAMPLEDATETIME.xlsx"
OUT=BIOMCC
DBMS=xlsx
REPLACE;
RUN;

DATA BIOMCC2;
SET BIOMCC;
DT=SUBSTR(SAMPLE_DATE,1,10);
TM=SUBSTR(SAMPLE_DATE,12,5);
DROP SAMPLE_DATE;
SAMPDT_CC=INPUT(DT,YYMMDD10.);
SAMPTM_CC=INPUT(TM,HHMM5.2);
FORMAT SAMPDT_CC DATE9.;
FORMAT SAMPTM_CC TIME5.;
run;

Thanks for support!! 

Johan

[PaigeMiller]

Most of us will not download Excel files, as it is a security risk. Please show us a portion of the SAS data set (not the Excel file) so we can see the date and time values that you are having trouble with.

[JohanK]

Hello Paigemiller, thanks. I have a solution 

[Tom · Accepted solution]

So assuming that the cells in the XLSX file are TEXT so that PROC IMPORT creates a character variable with values like:

data have;
  input sample_date $15. ;
cards;
2021-02-13T09:46
;

You can convert that string directly to a DATETIME value using the E8601DT informat. (To use the B8601DT informat  just remove the punctuation.)

You can convert that string into DATE and TIME values by parsing around the T and using the YYMMDD and TIME informats.  The HHMM informat is for strings without the punctuation.  To display the leading zero on times before 10 am use the TOD format instead of the TIME format.

data want;
  set have;
  dt = input(sample_date,e8601dt.);
  date = input(sample_date,yymmdd10.);
  time = input(scan(sample_date,2,'T'),time5.);
  format dt datetime19. date date9. time tod5. ;
run;

Result

Obs      sample_date                        dt         date     time

 1     2021-02-13T09:46     13FEB2021:09:46:00    13FEB2021    09:46

[JohanK]

Thank you so much Tom, it works!