topic Re: Import excel data and time in SAS Programming

Import excel data and time

DmytroYermak — Tue, 21 Nov 2017 10:49:48 GMT

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:

The real format of keeping is:

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?

Re: Import excel data and time

RW9 — Tue, 21 Nov 2017 11:05:04 GMT

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.

Re: Import excel data and time

DmytroYermak — Tue, 21 Nov 2017 11:16:10 GMT

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.

Re: Import excel data and time

RW9 — Tue, 21 Nov 2017 11:27:31 GMT

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.

Re: Import excel data and time

DmytroYermak — Tue, 21 Nov 2017 11:30:02 GMT

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

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

Re: Import excel data and time

RW9 — Tue, 21 Nov 2017 11:39:49 GMT

Aside from the fact we are changing the RAW data 🐵

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;

Re: Import excel data and time

DmytroYermak — Tue, 21 Nov 2017 11:48:33 GMT

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

Re: Import excel data and time

Kurt_Bremser — Tue, 21 Nov 2017 11:49:24 GMT

@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.