Import excel with DateTime format in SAS Data Integration Studio

[ashna]

Hi Everyone,

I'm trying to import excel file using SAS Data Integration studio, that have 3 different columns with DateTime format. Among those 3 col's only 2 columns are reading in correct format other column is reading is as Character due to empty rows. 

Sample data : 

Date1                              Date2                               Date3
Jan 11, 2019 12:17 PM        Jan 18, 2019 1:00 PM             
Jan 18, 2019 1:00 PM          Jan 11, 2019 9:23 PM            Jan 1, 2019 9:23 PM
Jan 11, 2019 12:17 PM        Jan 18, 2019 1:00 PM

Date 1 and Date2 are reading in correctly without any issues 

But Date3 reads in as 43486.40625.

Can anyone suggest what to do to get it correct.

[ChrisNZ]

1. Do you have strings in this column?

2 .Did you read this https://communities.sas.com/t5/SAS-Programming/Proc-import-xlsx-doesn-t-work-in-guessingrows/td-p/36... ?

[ashna]

Nope, I don't have any strings in the column.

As mentioned before, few of the cells or observations in this column are empty.

[andreas_lds]

Abandoning excel as data source is not an option? Such problems always arise, because excel is one of the worst file-formats for data storage. Excel lacks clear type definitions for columns, makeing it difficult for sas to guess the right type.

Check the column: has any cell a type other than date?

[Kurt_Bremser · Accepted solution]

The usual advice: export to csv from Excel and read the file with a data step, so you keep control and get a consistent correct result.

[ChrisNZ]

1. You must have data in the third column. Even spaces are characters and can trigger reading the field as a character.

data t; 
  X=put(datetime(),datetime20.);output;
  X=' ';                        output;
run;
proc export data=t outfile="%sysfunc(pathname(WORK))\t.xlsx" dbms=xlsx replace; run;
proc import out=r datafile="%sysfunc(pathname(WORK))\t.xlsx" dbms=xlsx replace; run;

2. The value you are showing (is it a character?) is a valid excel date time.

data T; 
  EXCELDATE=43486.40625; 

  SASDATE=intnx('year',EXCELDATE,-60);
  put SASDATE date9.; 

  SASTIME= (EXCELDATE-int(EXCELDATE)) *60*60*24;
  put SASTIME= time.; 
run;

SASDATE=01JAN2019
SASTIME=9:45:00

3. Did you try option DBSASTYPE ?