10-04-2016 07:31 PM
Trying to convert date formats when I am importing an Excel 2010 worksheet. Am using LIBNAME engine to import worksheet in which "Date_Effective" column is in a datetime type Excel format. Code is:
libname xl XLSX "C:\ROSTER.xlsx";
ERROR 22-7: Invalid option name SASDATEFMT.
Thought option SASDATEFMT was ok to use with Excel2010 files. Any thoughts on how to fix problem?
10-04-2016 08:57 PM
And this worked also with the XLSX engine and not with EXCEL?
Consulting the documentation I couldn't find a note which clearly states that this option isn't supported for the XLSX engine.
I suggest you contact SAS TechSupport directly: http://support.sas.com/techsup/contact/
10-05-2016 01:38 AM
Save the Excel dungheap to a resonable text-based format and import from that. In the data step you can very easily deal with all kinds of Excel formatting lunacy.
Over the years, I have learned that using the native Excel formats only causes unnecessary pain and wastes time.
10-05-2016 10:36 AM
I don't see support for SASDATEFMT as a dataset option for spreadsheets, only for relational database sources. EXCEL is not a database relational or otherwise.
10-06-2016 01:52 AM
@Patrick: That whole section of documentation you are referring to deals with libname EXCEL, not with libname XLSX.
EXCEL is available only on Windows, can also use .XLS files, and uses the Microsoft Data Access Objects, while XLSX is platform-independent, works only with .XLSX files, and uses no MS-supplied software.
And obviously SASDATEFMT is not (yet?) supported for libname xlsx. There is also no documentation for it.