BookmarkSubscribeRSS Feed
afurnia
Calcite | Level 5

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?

7 REPLIES 7
Reeza
Super User

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
Opal | Level 21

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
Super User

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
Super User

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.

Kurt_Bremser
Super User

@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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1791 views
  • 2 likes
  • 5 in conversation