topic Re: Invalid Option Name SASDATEFMT in SAS Programming

Invalid Option Name SASDATEFMT

afurnia — Tue, 04 Oct 2016 23:31:28 GMT

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?

Re: Invalid Option Name SASDATEFMT

Reeza — Wed, 05 Oct 2016 00:10:11 GMT

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.

Re: Invalid Option Name SASDATEFMT

Patrick — Wed, 05 Oct 2016 00:57:47 GMT

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/

Re: Invalid Option Name SASDATEFMT

Kurt_Bremser — Wed, 05 Oct 2016 05:38:08 GMT

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.

Re: Invalid Option Name SASDATEFMT

ballardw — Wed, 05 Oct 2016 14:36:57 GMT

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.

Re: Invalid Option Name SASDATEFMT

Patrick — Wed, 05 Oct 2016 22:31:29 GMT

@ballardw

For example under this link:

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

Re: Invalid Option Name SASDATEFMT

Kurt_Bremser — Thu, 06 Oct 2016 05:52:12 GMT

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

Re: Invalid Option Name SASDATEFMT

Patrick — Thu, 06 Oct 2016 09:03:48 GMT

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