Converting SQL date into SAS date

Question

In my data, SQL has formatted the dates into 5 digit characters such as 62627 of 63628. When I try to read the date into SAS and use a date format like DATE9. I get the correct month and day but the incorrect year. Any tips on how to get the correct date? Do I need to go into to SQL and make the conversion instead? Or can it be done in SAS?

I hope this post is useful to you: stackoverflow.com/questions/26960155/… — Barett
Incorrect by 60 years, by chance? It would be helpful to post what you have and what you expect. Also a bit unclear if the data is formatted as character in SQL Server or as a date or datetime. — Joe

Salva Salva · Accepted Answer · 2015-04-06T22:40:30

You may want to read the data dictionary and data definition of the database/system you are transforming. SAS base date is January 1, 1960. Some systems use January 1st too with different years/decades as a base year.

If you are mentioning that SAS is transforming day and month correct, and the year is offset by 60 years, my wild guess is that your system base date is Jan 1st, 1900.

Between 1900/01/01 and 1960/01/01 are 21,914 days.

This will do the trick:

data _null_;
    set test;
    new_date = sum(date,21914);
run;

Converting SQL date into SAS date

1 Answers