Sas Date conversion to date format

[Nehanzh]

While doing proc import of ab xlsx file sas converts short date to sas date format in character and unable convert it in date format. E.g.- date in excel- 10/4/2019. After proc import date- 43742 in char. After converting it to date with this code: format date_1 mmddyy8.;
Date_1=date; the output date_1=10/05/79. Any help?

[Kurt_Bremser]

First of all, do not (as in NOT) use two-digit years, or formats that display only 2 year digits for dates. It only causes confusion, for no gain.

If you got the raw Excel value in a date, add

'30dec1899'd

to it, and you'll get the correct date in SAS.

This value covers three things:

1. Excel starts (nominally, see 3.) at 1900-01-01, SAS starts at 1960-01-01
2. Excel starts with day 1, SAS with day 0
3. Excel has a bug that makes it consider 1900 a leap year

[jarapoch]

I'm sending you a link to a post talking about conversion from Excel dates to SAS dates.

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

Maybe you can use  SAS_date = Excel_date - 21916;  to convert your Excel date to a SAS date.

Just one thing, you'll have to convert your character variable "date" to a numeric variable using INPUT (eg: numeric_date = input(date, 8.))