topic Re: Date with wrong century when imported from a csv in SAS Enterprise Guide

Date with wrong century when imported from a csv

N_C — Wed, 01 Feb 2017 08:45:18 GMT

My data is imported from a csv file. In the csv file, I have a date field showing dates in the format, 22/05/39. When I click on the cell I can see that the date is in reference to the year 2039 but when I import it to SAS, it shows as 1939.

Can anyone help me with a way around this please?

Thanks.

Re: Date with wrong century when imported from a csv

Kurt_Bremser — Wed, 01 Feb 2017 08:58:37 GMT

Use a 4-digit format for the year in Excel. Period.

2-digit years should have been permanently disallowed since 2000.

Re: Date with wrong century when imported from a csv

N_C — Wed, 01 Feb 2017 09:04:30 GMT

Thanks, I agree! I can get around it like that but was hoping there was an alternative solution so that I didn't have to reformat the data every month.

Re: Date with wrong century when imported from a csv

Kurt_Bremser — Wed, 01 Feb 2017 09:16:20 GMT

You can modify the yearcutoff= system option, but that is only a stopgap measure for data that you have no control over. 4-digit years are the way to go.

Re: Date with wrong century when imported from a csv

RW9 — Wed, 01 Feb 2017 09:20:03 GMT

On your import program (which you haven't shown us so I can only guess), in the datastep have a line:

date=intnx('year',date,100);

This will move the date's year on 100. You will have to fix yourself or alter system options as the data is "bad".

Re: Date with wrong century when imported from a csv

N_C — Wed, 01 Feb 2017 09:23:54 GMT

Thanks but I can't do this as my data spans more than 100 years. I think the only option is to format the data in Excel to show 4 digits as per KurtBremser response.

Re: Date with wrong century when imported from a csv

ballardw — Wed, 01 Feb 2017 16:39:14 GMT

I hope that you have the correct values. If the file is csv and then opened in Excel and formatting applied sometimes values get "interpretted" by Excel in ways that may not quite be as desired.

Note that Excel is likely using a similar approach to the YEARCUTOFF so be careful about saving over your original file.