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.
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.
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... ?
Nope, I don't have any strings in the column.
As mentioned before, few of the cells or observations in this column are empty.
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.
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 ?
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.