DATA Step, Macro, Functions and more

Import excel data and time

Accepted Solution Solved
Reply
Regular Contributor
Posts: 210
Accepted Solution

Import excel data and time

Hi all,

 

Could you please help to import correctly the following data and time from .xlsx file.

 

Here below is the format of data and time:

1.jpg

 

The real format of keeping is:

2.jpg3.jpg

 

The code is as following:

proc import out= WORK.LABDATA
            datafile = "C:\Projects\lab.xlsx"
            dbms = XLSX REPLACE ;
run;

How to import the data and time directly into correct sas format?


Accepted Solutions
Solution
‎11-21-2017 06:47 AM
Super User
Super User
Posts: 9,227

Re: Import excel data and time

Posted in reply to DmytroYermak

Well, as with any question regarding Excel first and foremost, Excel is a really poor data format, which is why so many end users - not those who just use the front end - have so many issues with it.  The best method would be to convert it to CSV, then write a datastep import step, which reads in each data item specifying its informat, format, length, and label.  This is then reproducable, clean, and has no guessing component.  

 

Now you will continue using Excel and proc import no doubt, so you can try some of the options (from a quick search):

https://communities.sas.com/t5/SAS-Procedures/Proc-import-with-excel-date-field/td-p/42250

 

however do bear in mind you will always have issues with Excel file formats.

View solution in original post


All Replies
Solution
‎11-21-2017 06:47 AM
Super User
Super User
Posts: 9,227

Re: Import excel data and time

Posted in reply to DmytroYermak

Well, as with any question regarding Excel first and foremost, Excel is a really poor data format, which is why so many end users - not those who just use the front end - have so many issues with it.  The best method would be to convert it to CSV, then write a datastep import step, which reads in each data item specifying its informat, format, length, and label.  This is then reproducable, clean, and has no guessing component.  

 

Now you will continue using Excel and proc import no doubt, so you can try some of the options (from a quick search):

https://communities.sas.com/t5/SAS-Procedures/Proc-import-with-excel-date-field/td-p/42250

 

however do bear in mind you will always have issues with Excel file formats.

Regular Contributor
Posts: 210

Re: Import excel data and time

Thank you, RW9. Is it possible to convert xlsx into csv in sas? I have not found any appropriate links (. The matter is that I should not change so called 'raw' data outside sas.
Super User
Super User
Posts: 9,227

Re: Import excel data and time

Posted in reply to DmytroYermak

No, you would save the XLSX file as CSV.  This:

"The matter is that I should not change so called 'raw' data outside sas."

Has no real meaning, to move the data in from Excel to SAS you are changing it, be this by changing XLSX format to sas7bdat or via CSV.  If utilizing Excel functionality on an Excel file is not permitted, then the question arises why is Excel being used in the first place.

Regular Contributor
Posts: 210

Re: Import excel data and time

Actually I have used standard import procedure and the main issue I have faced is that the time was imported as character:

 

4.jpg

 

How can it be converted into hh:mm:ss ?

Super User
Super User
Posts: 9,227

Re: Import excel data and time

Posted in reply to DmytroYermak

Aside from the fact we are changing the RAW data Smiley Surprised

Excel stores times as fractions of a day:

http://excel.officetuts.net/en/training/how-excel-stores-date-and-time

 

So to convert you would need something like:

data want;
  set have;
  real_time=input(excel_time,best.) * (24*60*60);
  format real_time time5.;
run;
Regular Contributor
Posts: 210

Re: Import excel data and time

[ Edited ]

Thank you for the help,RW9. I have been allowed to convert the file into csv and your post was one of the arguments ).

Super User
Posts: 9,611

Re: Import excel data and time

Posted in reply to DmytroYermak

DmytroYermak wrote:
The matter is that I should not change so called 'raw' data outside sas.

Bullshit. Sorry for the harsh word, but that's what it is. You only READ the Excel, and then save to CSV in a location of your choosing. No change to the original file or its location.

With a proper Office suite (like Libreoffice), such a conversion can even be done from the commandline and therefore out of SAS by using filename pipe, X, or call system.

 

This conversion actually makes your import process into SAS more transparent and stable. Directly importing from Excel will always be the less efficient and less safe method.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 342 views
  • 2 likes
  • 3 in conversation