topic Re: Date format problems importing from Excel in SAS Programming

Date format problems importing from Excel

Scout — Wed, 05 Apr 2017 09:36:58 GMT

Hello,

I have imported an Excel (xlsx) dataset with 100+ variables, but one date variable refuses to be recognised as a date by SAS 9.4. I have formatted it as a date in Excel, repeatedly done the 'text to columns' procedure to specify it is a date, and increased my guessingrows to well over the necessary amount. I've also tried coping and pasting the values only in a new column, and repeated everything above, and still can't get it to work.

I get NOTE: Invalid numeric data, date='12/27/2016' , at line xxxx column x for every date entry.

I am unable to convert the file to CSV as I lose data in other columns (in other languages). The Excel file has many date variables and I can get them all to work except this one. I've also tried formatting the variable in SAS but just get errors that the format cannot be loaded (Date9. or anything else) despite working fine in other procedures. Any thoughts on what I can do?

Thanks in advance

Re: Date format problems importing from Excel

RW9 — Wed, 05 Apr 2017 09:51:15 GMT

Without seeing the file its going to be hard to help. Excel is a terrible data medium. One thing that jumps to mind is EU/US date differences for instance: MM/DD/YY and DD/MM/YY.

Is it possible that you have values in that column which could be interpreted either way? Because 12/27/2016 would not be decodable to DD/MM/YY, but others might. This might be confusing the "guessing" proc import program.

Thats the only thing I can think off hand, however there could be any number of problems with an excel sheet - hidden things, hidden formats, differences in the data in each cell, just to name a few. Sounds like you have a real mess of a file.

If you can get the problem data, just a few rows, in a datastep (see: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712) then could suggest a input() function to convert it, as:

d_var=input(c_var,mmddyy10.);

Should work.