Help using Base SAS procedures

Problem importing excel columns with time / datetime format

Accepted Solution Solved
Reply
Regular Contributor
Posts: 156
Accepted Solution

Problem importing excel columns with time / datetime format

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

Am


Accepted Solutions
Solution
‎03-04-2017 09:23 AM
Super User
Posts: 17,829

Re: Problem importing excel columns with time / datetime format

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+

View solution in original post


All Replies
Solution
‎03-04-2017 09:23 AM
Super User
Posts: 17,829

Re: Problem importing excel columns with time / datetime format

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+

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 442 views
  • 0 likes
  • 2 in conversation