Issue 20927 fix resolves read_sas error for dates/datetimes greater than 2262-04-11
State: closed

- [x] closes #20927 
- [x] tests added / passed
- [x] passes `black pandas`
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x ] whatsnew entry


Labels:
name: Datetime
description: Datetime data dtype
name: Period
description: Period data type
name: IO SAS
description: SAS: read_sas

Comments:
Couple of thoughts:

1) It's more idiomatic for pandas use Periods to work with out of range dates, so we may want to return period objects in this case: https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#representing-out-of-bounds-spans

2) We should probably raise a `UserWarning` nonetheless to warn the user that different data dtype is being returned in this case.
Thanks for the thoughts @mroeschke 

I'll add the UserWarning about returning different types.  

As the code is at the moment, it will return datetime.date/datetime for _all_ rows if there is at least one SAS date/time that is > 2262-04-11.  
However, if read_sas is called as an iterator, chunks with all SAS date/time < 2262-04-11 will return with np.datetime64 and other chunks with at least one SAS date/time that is > 2262-04-11 will return with datetime.date/datetime - i.e. the caller will get a mix of datatypes.

I get some odd results for Periods with dates larger than 2262-04-11, e.g.
``` python
import pandas as pd
g = pd.Period(year=2262, month=4, day=11, freq='D')
print(g.start_time)
g2 = pd.Period(year=2262, month=4, day=12, freq='D')
print(g2.start_time)
```
gives
2262-04-11 00:00:00 which is correct, but then
1677-09-21 00:25:26.290448384 which is clearly wrong for 2262-04-12

This is maybe a bug in Period (?) but for the time being returning datetime.date/time gives a better representation of the original SAS dataset
Could you open up a new issue for the Period overflowing? That looks like a bug.

`datetime.date` do not have a lot of support in pandas compared to `datetime.datetime`, so I am a little weary of returning those objects.
Ok, I'll raise a new issue for the Period overflow.

I'll also alter read_sas to return datetime.datetime for both dates and datetimes > 2262-04-11.  There will still be the possibility of a mix of np.datetime64 and datetime.datetime types in the final dataset if read_sas is called as an iterator.
Hello @paul-lilley! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:





There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2020-05-25 20:25:35 UTC
can you merge master and update to comments
Hi, apologies for the delayed response.  I'll try to address Tom's points, then merge master and update the comments.
can you merge master and will look again
Closing as I think stale, but @paul-lilley ping if you can address comments and would like to pick back up
Hi @WillAyd 
I think this is still an issue - I've updated the code and merged master, can you reopen the request?
Thanks
@bashtage if any comments.
Hi @jreback 
I've made the minor changes from @bashtage and merged master, can you take a look? Thanks.
Looks like this one fell through but is close - @paul-lilley can you fix the merge conflict?
Hi @WillAyd - I've merged master (it went through without merge conflicts)
Hi @WillAyd , @bashtage I've altered the comment and merged master
thanks @paul-lilley very nice!
@paul-lilley im trying to understand the comment on test_max_sas_date

```
    # NB. max datetime in SAS dataset is 31DEC9999:23:59:59.999
    #    but this is read as 29DEC9999:23:59:59.998993 by a buggy
    #    sas7bdat module
```

Does this mean that the test here is still wrong and it really should be coming back with `datetime(9999, 12, 31, 23, 59, 59, 999000)`?
@bashtage do you have convenient access to SAS and willingness to help me track down https://github.com/pandas-dev/pandas/pull/28047#issuecomment-1780391974
Hi @jbrockmendel
Correct, it really should be coming back with datetime(9999, 12, 31, 23, 59, 59, 999000) as max datetime in SAS dataset is 31DEC9999:23:59:59.999 (ie. millisecs precision not microsecs).
However, I'd recommend moving all the SAS dataset reading to use https://github.com/Roche/pyreadstat (currently used in Pandas for SPSS data) as it is way faster
I don't have access to SAS easily, but @paullilley answered.  
I do have access to SAS if needed.
Thanks @paul-lilley.  When I read the file with pyreadstat it comes back as `datetime(9999, 12, 29, 23, 59, 59)`, i.e. the day is 29 instead of 31 and the seconds are rounded down.  Is there a chance the file was written with Dec 29 instead of Dec 31, or is there a problem that common to both the pandas and pyreadstat readers?