12-02-2015 12:15 AM
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.
12-03-2015 04:55 PM
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).
12-02-2015 04:37 AM
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).
12-02-2015 01:02 PM
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.
12-03-2015 05:02 PM
12-03-2015 05:00 PM