BookmarkSubscribeRSS Feed
acegsm
Obsidian | Level 7

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?

8 REPLIES 8
Reeza
Super User
What are your dates and how are they formatted?
acegsm
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Obsidian | Level 7
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
Obsidian | Level 7
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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1655 views
  • 3 likes
  • 4 in conversation