SAS importing dates from excel

问题:

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

回答1:

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;