Importing formatted dates with proc import, dbms=XLSX

[ljlynn97]

I am trying to import data using proc import and having trouble with a date format. In Excel the date appears as MM/DD/YYYY but when I run the import, I get a 5-character string that does *not* correspond to the SAS date (e.g. the date is 10/08/2019, and after the import the cell shows "43745", which, even if I convert it to a date, is equivalent to 10/08/2079).

Is there a way I can specify in the proc import how I want to import this particular column?

proc import
	out=data_collected
	datafile="SchoolList.xlsx"
	DBMS=XLSX
	replace
	;
	range="'School_Data'$A:K";
run;

[smantha · Accepted solution]

The date you are getting is an excel representation of the date which starts from 1 under 1JAN1900. Even though they are displayed as dates excel is storing them as numbers underneath. You can fix your excel sheet not to do that by playing around with date formats, or subtract 21916, from the number you get after reading into sas, and format the number as a date.

[ljlynn97]

This makes sense. The Excel sheet is fixed but I can subtract 21916 when I do some other data cleanup. Thank you!