SAS import excel date format changes

Question

I need to import an excel, the excel has a few columns and the 1st column A is a date column. Column A has the date format DDMMMYYYY e.g. '01Jan2017' and in excel the data type is date type. But when I import it to SAS, all the other columns remain the same data type (numeric, character, etc.) and value. But column A becomes a number e.g. ('42736' for '01Jan2017'). How do I import the data as it is and without converting the data type to other types?

libname out '/path';

proc import out=out.sas_output_dataset
datafile='/path/excel_file.xlsx'

DBMS=XLSX 
REPLACE;
sheet="Sheet1";

run;

Jorvik77 Jorvik77 · Accepted Answer · 2017-05-04T12:20:46

It is hard to know without seeing the data. The below is general information, it may not answer your precise problem.

To avoid common errors you should set mixed=yes in your libname. You may also want to include stringdate=yes statement.

The mixed=yes allows for any out of range excel date values.

stringdates=yes brings all dates into SAS in character format, so you will need to use the input() function to convert this into a SAS date.

Date = input( Date , mmddyy10. )

SAS import excel date format changes

1 Answers