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,
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).
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).
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.
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. 🙂
And the moral of the story: "Use the right tool for the right purpose". Excel is almost never the right tool.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.