topic Re: Sas Date conversion to date format in SAS Programming

Sas Date conversion to date format

Nehanzh — Fri, 06 Nov 2020 10:45:48 GMT

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?

Re: Sas Date conversion to date format

Kurt_Bremser — Fri, 06 Nov 2020 12:06:57 GMT

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:

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

Re: Sas Date conversion to date format

jarapoch — Fri, 06 Nov 2020 12:10:31 GMT

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-Date/td-p/475824

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.))