Import Time Values from XLS File

[djbateman]

I am writing a program for some clients that reads in a couple of excel files.  I give them the option to read in xls or xlsx because some of the files they are downloading are still using an old process and only provide xls files.  When I try to import the files, it converts all the time values to 01MAR2019 regardless of the time and saves it in a date9. format.  Is there a way to get SAS to recognize the column as a time field and treat it accordingly?  Is there an option or something in my import block that is missing?

PROC IMPORT OUT= WORK.FILE1
            DATAFILE= "&dir.\&file1..xls" 
            DBMS=EXCELCS REPLACE;
     RANGE="Page1_1$"; 
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

[Tom]

Perhaps the time part is still there.  Excel stores time as a fraction of a day. The date formats will just ignore the fractional part.

Do the values only contain integers?

For example you could try this:  Assume that the variable is called DATE try this to create a new variable called TIME.

time='24:00't*(date-int(date));
format time time.;

What happens if don't add those options? Or make changes to them? There is an interaction between the SCANTIME and USERDATE options.

https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0msy4hy1so0ren1acm90iijxn8j.htm&docset...

SCANTIME=YES | NO

specifies whether to scan the time data while importing data from a time column from the Microsoft Excel workbook.

YES scans the time column and assigns the TIME. format for a time column.

NO specifies not to scan the time column. The DATETIME format is assigned if USEDATE=NO. The TIME. format is assigned if USEDATE=YES.

[djbateman]

Tom,

I tried your method, and it showed that all non-missing values are integers (i.e., TIME only produced zeroes).  As for the options I have in there, I have tried running with all combinations of options including no options at all.  It just didn't work.  This is not an urgent or vital issue.  Not that I want to just give up on something, but if we can't come to a solution, it won't ruin my employment status.

Thanks,

Dallas

[Tom]

Do you have to use DBMS=EXCELCS?

You could try reading the same file use DBMS=XLS or DBMS=XLSX and see if it handles the column better.

Also check if all of the cells in the column are formatted (or whatever Excel calls) the same way. Perhaps some type of mixed types is causing the issue you see.

[djbateman]

I played around with different options for DBMS.  XLS and EXCEL gave me an errors (see below for details).

Using DMBS=XLS

390 PROC IMPORT OUT= WORK.FILE1
391 DATAFILE= "&dir.\&file1..xls"
SYMBOLGEN: Macro variable DIR resolves to S:\cdm\Programming\Excel_Comments
SYMBOLGEN: Macro variable FILE1 resolves to VX17-445-105_FIRST_DOSE_(DOSEF)_04MAR2019
392 DBMS=XLS REPLACE;
393 RANGE="Page1_1$";
394 /* SCANTEXT=YES;*/
395 /* USEDATE=YES;*/
396 /* SCANTIME=YES;*/
397 RUN;

Requested Range not found on Excel ->
S:\cdm\Programming\Excel_Comments\VX17-445-105_FIRST_DOSE_(DOSEF)_04MAR2019.xls
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Using DMBS=EXCEL

415 PROC IMPORT OUT= WORK.FILE1
416 DATAFILE= "&dir.\&file1..xls"
SYMBOLGEN: Macro variable DIR resolves to S:\cdm\Programming\Excel_Comments
SYMBOLGEN: Macro variable FILE1 resolves to VX17-445-105_FIRST_DOSE_(DOSEF)_04MAR2019
417 DBMS=EXCEL REPLACE;
418 RANGE="Page1_1$";
419 SCANTEXT=YES;
420 USEDATE=YES;
421 SCANTIME=YES;
422 RUN;

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 3.09 seconds
cpu time 0.09 seconds

[Tom]

Did you telling PROC IMPORT the name of the SHEET to be read instead of the named RANGE when using DBMS=XLS?

[djbateman]

Good catch.  I just changed RANGE to SHEET.  However, it made no difference.  If I play with DBMS, XLS and EXCEL give me errors.  If I keep it as EXCELCS, the code runs clean, but the time values are still all set to 01MAR2019.

[ErikLund_Jensen]

@djbateman 

An idea:

1) Have a look at input data. Proc import generates code that is written to the log, Copy the code to the program editor and modify it, so the time field is read as $22. and is without a format, and see what you get. 

2) Copy a value and play with it in a small data step, until you find a usable informat, and if that is not possible, add some code to change it to a time value.

3) Change the generated code from the import step and use that instead of proc import.

[Ksharp]

Try option:

MIXED=yes;

and hope turn time into character type and input it after data step .

[djbateman]

I tried MIXED=YES.  It doesn't seem to work.  I get the error messages below, and this is only after I changed DBMS to EXCEL.  If I leave it as EXCELCS, I get a different error saying that the statement is not valid or is not used in the proper order

449 PROC IMPORT OUT= WORK.FILE1
450 DATAFILE= "&dir.\&file1..xls"
SYMBOLGEN: Macro variable DIR resolves to S:\cdm\Programming\Excel_Comments
SYMBOLGEN: Macro variable FILE1 resolves to VX17-445-105_FIRST_DOSE_(DOSEF)_04MAR2019
451 DBMS=EXCEL REPLACE;
452 RANGE="Page1_1$";
453 SCANTEXT=YES;
454 USEDATE=YES;
455 SCANTIME=YES;
456 MIXED=YES;
457 RUN;

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 2.65 seconds
cpu time 0.06 seconds

[Vince_SAS]

The "Class not registered" error indicates that you have a bitness mismatch between SAS and Microsoft Excel.  The EXCEL engine requires the same bitness.

Can you post a sample XLS or XLSX file?  I made XLS and XLSX files with an Excel time value and PROC IMPORT correctly converts them to SAS time values using this code:

proc import file='C:\temp\time.xls'
  out=work.time_xls_xls
  dbms=xls
  replace;
run; quit;

proc import file='C:\temp\time.xls'
  out=work.time_xls_excelcs
  dbms=excelcs
  replace;
run; quit;


proc import file='C:\temp\time.xlsx'
  out=work.time_xlsx_xlsx
  dbms=xlsx
  replace;
run; quit;

proc import file='C:\temp\time.xlsx'
  out=work.time_xlsx_excelcs
  dbms=excelcs
  replace;
run; quit;

Vince DelGobbo

SAS R&D

[djbateman]

Vince,

Thank you for your help.  I tried your code where I attempted all 4 variations.  I noticed that all the DBMS=EXCELCS read in all the time values as 01MAR2019.  When I used DBMS=XLS or XLSX, the time values were all imported as 43525.xxxx.  I assume that 43525 is the unformated SAS date equivalent of 01MAR2019, but I'm not sure where the decimal values are coming from.

I have attached a sample of one of my files.  The problem I'm running into is with Column H "Time ~ First Dose Time".  But now that I'm writing this and looking more into the data, I believe the problem arises because there is an occasional entry of "N/A" in that column.  That must be what is throwing things off.  If I run the code on a file where all the N/As are removed (file also attached), it works as expected.  But what I don't understand is that Column G has a matching N/A for the adjacent date value, and those don't import wonky.

So, I guess I have a new question.  How can I get SAS to import a time column when there are a few N/As sprinkled among the values?  And will there be a way to get SAS to recognize a date or time variable that can automatically format it?  When I went back to my original code (with DBMS=EXCELCS and SCANTEXT=YES; USEDATE=YES; SCANTIME=YES;) I end up with the same 01MAR2019 value for all times.

Thanks,

Dallas

[Tom · Accepted solution]

If you have mixed character and numeric values in the same column then SAS will create the variable as character.  The date/time values from Excel will then show up as the decimal string that represents the internal way that Excel stores dates.

As I said before Excel stores time as percent of 24 hours.  So the code I posted before should work, once you have converted the string into a number.  The integer part is the number of days (as Excel counts them) since 1900.

Here is an example of how to deal with those strings.

data xx;
  input time_string $20.;
  time='24:00't*mod(input(time_string,??32.),1);
  format time time.;
  put (_all_) (=/);
cards;
43525.5
43525.125
43525.250
;

[djbateman]

Vince,

I should have summarized with code what I did and the outcomes:

My original file with DBMS=XLS produces the time variable with 43525.xxxx

proc import file='C:\\XLS_DOSEF_SAMPLE.xls'
			out=work.time_xls_xls dbms=xls replace;
run; quit;

All the N/As removed using DBMS=XLS properly produces a time value, but it is unformatted, and in practice, I won't know which variables are dates or times or anything like that, so I would like these to be formatted.

proc import file='C:\XLS_DOSEF_SAMPLE_noNA.xls'
			out=work.time_xls_xls_noNA dbms=xls replace;
run; quit;

All the N/As removed using DBMS=EXCELCS just brings me back to my original issue where all the time values are read in as 01MAR2019.

proc import file='C:\XLS_DOSEF_SAMPLE_noNA.xls'
			out=work.time_xls_excelcs dbms=excelcs replace;
		     SHEET="Page1_1$"; 
		     SCANTEXT=YES;
		     USEDATE=YES;
		     SCANTIME=YES;
run; quit;