SAS Wrong Date

[acegsm]

Good evening,

I am importing files from the end of last century, and every time I import them into SAS, the date is wrong by about 4 years. It appears this is because the box '1994' date in advanced options in excel sheet is checked. However, if I uncheck it then the wrong date appears. So, I imported the files with the '1994' box checked and the file still imports into SAS as the wrong date (about 4 years earlier). I tried importing the file again using a numeric value for the date & the goal of converting the column from a numeric value to a date value in SAS; however, that is not working.

Any suggestions?

[Reeza]

What are your dates and how are they formatted?

[acegsm]

Reeza,

I have probably 40-60,000 rows in order of ID#. Some of the dates will repeat themselves within and between IDs. They are currently in time8. format; however, I have tried other formats as well. Originally I was having problems with the dates in excel too, but checked the box "1994" in advanced options and it fixed the dates. An example of a date could be January 1st, 1997 but it may show up as December 31st, 1992 (about 4 years +/- a day).

[RW9]

As a suggestion, save your data as CSV (Comma Separated Variable data, from SaveAs in Excel).  Now look at the text file created.  You will see the data "as it really is", Excel biggest function is hiding the underlying data from the user.  What does the text file look like, does that look correct?  If so write a small datastep import (data xys; infile...;).  If its still not working post the code you are using and a few rows of the data so we can see (preferable text CSV or as a datastep).

[ballardw]

One additon: BEFORE exporting to CSV set all of the date columns to use the same date format and use one with 4-digit years. The "same date format" is for use humans to read easier. Setting the entire column is because of the potential for Excel to interpret user inputs in odd ways and to avoid any issues with Excel date value offsets.

[acegsm]

Thanks, BallardW. I will try this tonight and get back to you promptly. I have been converting all of the columns to the same format because I sometimes need to merge files, so I try to keep all of the variables formatted the same way (datetime20., time8., date9., et cetera).

[acegsm]

I will check this tonight when I am back in town, and respond promptly. Thank you for the suggestion.

I keep having problems with times &/or dates when transferring them from excel to SAS. Yesterday, it kept telling me that 18:08 was around 3:00. Sometimes it occurs after I convert a decimal time to military time (i.e. above it was 18.1...). I haven't figured out how to fix this problem yet either, but the problem noted above appears to be related to Excel formatting because it is showing the times previously appearing in excel before I checked the "1994" box in advanced options. However, I need the dates to be 3 years later, i.e. January 1st, 1997 instead of December 31, 1992. Checking the box fixes it in excel but not SAS, so it may be hidden in excel.

[acegsm]

Good evening,

Thank you guys for the help! It appears checking the "1994" box in advanced options & then converting the file to a csv worked. 🙂

[RW9]

And the moral of the story: "Use the right tool for the right purpose".  Excel is almost never the right tool.