Problem importing excel columns with time / datetime format

ammarhm — Sat, 04 Mar 2017 12:59:28 GMT

Hi all

I have a problem importing an excel file, specifically related to a column which is time and another which is datetime

I am trying to import an excel file using proc import:


PROC IMPORT OUT= Data
            DATAFILE= "c:\Home\Desktop\data.xlsx"
            DBMS=EXCEL REPLACE ;
GETNAMES=YES;
MIXED=yes;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
Sheet='Sheet1';
RUN;

Which works well, and the file is imported. However, the colums which are in time format in the excel file (yes, formated corectly in the excel file) are imported as date. Same thing happening with columns that are datetime in the excel file

Even if i try to set the data to a new set and force a format on these columns, it dosent work:


data Data2;
set Data;
format op_date_time datetime21.;
format op_time time8.;

run;

I guess one way to solve this is importing excel files using libname solution. But is there a way with proc import to instruct SAS to the time/datetime nature of some of the columns

Kind regards

Re: Problem importing excel columns with time / datetime format

Reeza — Sat, 04 Mar 2017 13:03:20 GMT

Not within PROC IMPORT but within a libname you can use DBSASTYPE.

Try the XLSX engine if that's an option, available is SAS 9.3+

topic Re: Problem importing excel columns with time / datetime format in SAS Procedures

Problem importing excel columns with time / datetime format

Re: Problem importing excel columns with time / datetime format