Desktop productivity for business analysts and programmers

Date with wrong century when imported from a csv

Accepted Solution Solved
Reply
Occasional Contributor N_C
Occasional Contributor
Posts: 7
Accepted Solution

Date with wrong century when imported from a csv

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. 


Accepted Solutions
Solution
‎02-01-2017 04:24 AM
Esteemed Advisor
Posts: 6,646

Re: Date with wrong century when imported from a csv

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,646

Re: Date with wrong century when imported from a csv

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor N_C
Occasional Contributor
Posts: 7

Re: Date with wrong century when imported from a csv

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. 

Solution
‎02-01-2017 04:24 AM
Esteemed Advisor
Posts: 6,646

Re: Date with wrong century when imported from a csv

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Date with wrong century when imported from a csv

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".

Occasional Contributor N_C
Occasional Contributor
Posts: 7

Re: Date with wrong century when imported from a csv

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. 

 

Grand Advisor
Posts: 10,210

Re: Date with wrong century when imported from a csv

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 174 views
  • 0 likes
  • 4 in conversation