Date-excel variable into SAS date issue

[donathomas288]

Hi,

I'm importing a excel file into SAS. While importing, a date variable say X  has 2020 (Date format) in Excel gets converted into a date variable in SAS environment into 12JUL1905.

How can i fix this? Herewith I attach the screenshots of date in excel and the SAS datasets after importing the excel file.  Is there any way to read it as a character variable as it is in excel? 

excel 

Spoiler

sas

[PaigeMiller]

https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS...

[Tom · Accepted solution]

From your photographs it looks like SAS imported the field as numeric.  So the column in Excel must not have had any character values in it.  Instead it has a mix of actual date values (number of days) and year values.  So it appears that SAS has interpreted that number is an Excel date (number of days since 1900) instead of an integer number of years or a SAS date (number of days since 1960).

94   data _null_;
95     date=2020;
96     put date date9.;
97     date=2020 + '30DEC1899'd ;
98     put date date9.;
99   run;

13JUL1965
12JUL1905

If you want the values to interpreted as dates then store a valid date (instead of a year number) in the Excel file. If you want SAS to instead to read the column as strings then modify the Excel sheet so that all of the values are character strings. (What out that all of the dates are actually converted or else you will get strings with the raw Excel day number as a digit string)

To fix after you have converted it to SAS instead you just need to check if the value is too small to be considered a count of days.  If you want the variable to stay numeric you need to decide which day in that year you want to use.  For example you could January first.

if datevar < '01JAN1906'd then datevar=mdy(1,1,datevar - '30DEC1899'd);

If you want to convert the field to a character variable you will need to use a new name for the new variable.  For the actual date values convert them using the date style format you want. (DO NOT USE ONLY TWO DIGITS FOR THE YEAR!)  For the year only values you need to decide to what you want the strings to look like.   Do you want just a four digit year? Then use the 4. or Z4. format.  If you want them to look like a date then you need to pick a day in that year.

if datevar >= '01JAN1906'd then datestr=put(datevar,yymmdd10.);
else datestr = put(datevar-'30DEC1899'd,Z4.);