BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DmytroYermak
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DmytroYermak
Lapis Lazuli | Level 10
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DmytroYermak
Lapis Lazuli | Level 10

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 ?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
DmytroYermak
Lapis Lazuli | Level 10

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

Kurt_Bremser
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 6104 views
  • 3 likes
  • 3 in conversation