Numeric dates not converted to dates properly

[KristinaT]

Hello,

The original excel table that I am importing into SAS Guide has dates in numeric formats like 44275, which is 20. March 2021.

In wizard while importing I set output to be date in form of DANDfdd.w. But the output date came out wrong: 31.March 2081.

The same wrong date I get if leave it as original through import wizard but try to change through code with format=DDMMYYP10.

proc sql;

select Maturity_date format=DDMMYYP10.
from work.'lrdb_sent_combine_30.11.202_0000'n (obs=100);
quit;

Why am I getting the wrong date?

[PaigeMiller]

Answer is here: https://communities.sas.com/t5/New-SAS-User/Excel-to-SAS-date-format-issues/m-p/539683#M7100

[Shmuel · Accepted solution]

Excel holds a date as number of days past since and including 01/01/1900
(= 1 as a number);

SAS holds the date as number of days past since and excluding 01/01/1960
(= 0 as a number);

If you enter any date in an excel cell and in sas variable and check the numeric difference - it is a constant.

You can convert the numeric value using next formulas:

converting date/time values from Excel to SAS.

SAS_date = Excel_date - 21916;
SAS_time = Excel_time * 86400;
SAS_date_time = (Excel_date_time - 21916) * 86400;

[Tom]

---

@Shmuel wrote:

Excel holds a date as number of days past since and including 01/01/1900
(= 1 as a number);

SAS holds the date as number of days past since and excluding 01/01/1960
(= 0 as a number);

If you enter any date in an excel cell and in sas variable and check the numeric difference - it is a constant.

You can convert the numeric value using next formulas:

converting date/time values from Excel to SAS.

SAS_date = Excel_date - 21916;
SAS_time = Excel_time * 86400;
SAS_date_time = (Excel_date_time - 21916) * 86400;

---

There is no need to remember "magic" numbers.  Just use DATE ('30DEC1899'd) or TIME ('24:00:00't)  literals.

257  data _null_;
258     offset = '30DEC1899'd ;
259     seconds = '24:00:00't ;
260     put (offset seconds) (= comma11.);
261  run;

offset=-21,916 seconds=86,400

PS The reason to use 30th instead of 31st is that Excel treats 1900 as a leap year (which it isn't).