SAS importing dates from excel

Question

I have an xlsx dataset that I import using proc import. There is a column in excel that holds date values like: 2018-11-30 00:00:00 When I import it into SAS it automatically converts it into a number 43434. When I try to cast this to a date: put(col,date9.), i get: 2078-12-01

What is happening? How can I get back the correct date. I tried mixed=yes option but it does not work with dbms=xlsx. When i do dbms=excel, it does not work as expected

Can you fix the column in the Excel so that all of the values are valid dates before importing it to SAS? When the column has mixed type SAS will be forced to import it as character. When it does that it gets the character representation of the number that Excel uses to store dates instead of the human readable formatted value. — Tom

Stu Sztukowski Stu Sztukowski · Accepted Answer · 2019-01-25T18:07:03

Sometimes SAS imports the date as a raw Excel date. I don't know exactly why or when it does this, but I think it has something to do with the format of the date. If this happens, subtract the date by 21916 to convert from an Excel date to a SAS date.

data want;
    set imported_from_excel;

    date = excel_date - 21916;
run;

SAS importing dates from excel

1 Answers