DateTime formatting

[AMFR]

I have a 3 numeric datetime columns with the format DateTime. When I export the SAS data into EXCEL the year of the column for 2026 is showing as 1926. How can i fix this? What I am doing wrong. Thanks in advance

1/1/2026 12:54 is showing up in EXCEL as 1/1/1926 12:54

[ChrisHemedinger · Accepted solution]

Check your YEARCUTOFF setting -- it might be set to 1926. 
See: YEARCUTOFF option setting for SAS Viya 3.5 and older SAS 9.4 releases 

[AMFR]

Yes the YEARCUTOFF setting was 1926.

After changing it to 1940, EXCEL is giving me year as 2026.

Thank you very much

[Quentin]

Glad you got it working.

I suspect the problem was actually in your SAS dataset, not in the process of exporting the data to Excel.  Can you share how you exported the data to Excel?  

My understanding is the yearcutoff option only has an effect when SAS encounters a two-digit value and is asked to interpret it as a four-digit year.

Once you have a date variable (or date-time variable), the data is stored as a number (number of days or seconds since 01Jan1960).  So yearcutoff should not have any impact, as I understand it.

When I run below, the date-time is correctly exported to Excel, as I would expect:

options yearcutoff=1926 ;

data have ;
  mydate="01Jan2026:12:54"dt ;
  format mydate datetime. ;
run ;

proc export data=have outfile="Q:\junk\want.xlsx" dbms=xlsx replace;
run ;

My guess is if changing yearcutoff fixed the problem, that means somewhere in your code or data you have a two-digit year and you asked SAS to calculate a date-time from that value.

@ChrisHemedinger , is there really a case where the YEARCUTOFF value would effect the export process itself (assuming the exported data is a SAS date or date-time)?

[ChrisHemedinger]

I cannot say for sure, @Quentin . Since there are many ways to "export" to Excel (in UIs and code engines), it's possible that some methods would use the formatted value and if that featured a 2-digit year, then YEARCUTOFF could play a part. So...better to have a fully qualified format plus a reasonable setting for YEARCUTOFF!

[Quentin]

If you have a date-time variable in a SAS dataset, and you're exporting the data to Excel, I don't see how Excel could end up with a value that is 100 years off. 

But it's hard to guess at what might be going wrong without more info.  Can you show the code for how you export the data to Excel?

Can you post example data that replicates the problem?  For example, if you export the below dataset, do you see the problem?

data have ;
  mydate="01Jan2026:12:54"dt ;
  format mydate datetime19. ;
run ;

I think it's most likely that the date-time value in your SAS dataset is actually 1/1/1926 12:54, due to a problem earlier in your code.

[ChrisHemedinger]

In @Quentin's reply he includes a hint, which is that a fully qualified SAS format can make the difference. DATETIME is not the same as DATETIME19, and your data might be using the former -- which could leave it vulnerable to a YEARCUTOFF ambiguity when the formatted date value is exported.

[Ksharp]

As Quentin said, it is hard to give you some advice before you posted an example to replicate your issue.
Post your sample data and the code you are using.

[AMFR]

Thank you. Next time I will post the codes as well.

[Kurt_Bremser]

Please provide the complete log of the export step, and also tell us which format is assigned to the datetime variable. If it is a custom format, do also supply the PROC FORMAT code.