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?
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.
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.
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.
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 ?
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;
Thank you for the help,RW9. I have been allowed to convert the file into csv and your post was one of the arguments ).
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.