Invalid Option Name SASDATEFMT

[afurnia]

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";

data ROSTER_IN;
set xl.'sheet2015$'n(SASDATEFMT=(Date_Effective=MMDDYY8.);
run;

from log...

set xl.'sheet2015$'n(SASDATEFMT=(Date_Effective=MMDDYY8.));
                       ----------
                       22

ERROR 22-7: Invalid option name SASDATEFMT.

Thought option SASDATEFMT was ok to use with Excel2010 files.  Any thoughts on how to fix problem?

[Reeza]

Never heard of that option before...

I think DBSASTYPE may be a workaround but honestly when importing from Excel you don't have a ton of flexibility.

[Patrick]

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/ 

[Kurt_Bremser]

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.

[ballardw]

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.

[Patrick]

@ballardw

For example under this link:

 http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0cfqhcraieg81n1ie...

[Kurt_Bremser]

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

[Patrick]

@Kurt_Bremser

That's why I've asked the OP if he/she used exactly the same code with the same engine when it was working.