DATA Step, Macro, Functions and more

Invalid Option Name SASDATEFMT

Reply
New User
Posts: 1

Invalid Option Name SASDATEFMT

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?

Super User
Posts: 19,855

Re: Invalid Option Name SASDATEFMT

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.

Respected Advisor
Posts: 4,173

Re: Invalid Option Name SASDATEFMT

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/ 

Super User
Posts: 7,845

Re: Invalid Option Name SASDATEFMT

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Invalid Option Name SASDATEFMT

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.

Respected Advisor
Posts: 4,173

Re: Invalid Option Name SASDATEFMT

Super User
Posts: 7,845

Re: Invalid Option Name SASDATEFMT

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,173

Re: Invalid Option Name SASDATEFMT

Posted in reply to KurtBremser

@KurtBremser

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

Ask a Question
Discussion stats
  • 7 replies
  • 525 views
  • 2 likes
  • 5 in conversation