Support more sas7bdat date/datetime formats
State: closed

updated p.r. #13089 after `origin` parameter was added by #15828
 - [ v ] tests added / passed
 - [ .. ] whatsnew entry


Labels:
name: Enhancement
name: Datetime
description: Datetime data dtype
name: IO SAS
description: SAS: read_sas

Comments:
`pandas/tests/io/sas/test_sas7bdat.py:test_productsales` fails but it's not my issue, right?.)
@Winand looks like a reasonable fix. Do you have some more example files that we can add for testing? (IOW w/o some of these more common formats)?

It looks like the failure is related to your change.
cc @bashtage if you can have a look.
does this need an option? or is it 'automatic'?
cc @kshedden
i can create more test files in SAS. Well... i'll add more columns with different formats. Though all formats are used only to detect that a numeric column is a date (or datetime) column.

oh, of course the failure should be related. i'll look into it.

>does this need an option? or is it 'automatic'?

i don't understand the question. There's `convert_dates` option. When it is on, columns with formats recognized as date/time are converted from numeric to date/time.
so here is the confusion I think.

we have a different doc-string for ``read_sas`` and the actual reader (I think because the XPORT format doesn't support any options).

So I think it would be a good idea to expose relevant options directly to ``read_sas`` (and just ignore them in xport; we can put this in the doc-string).

```
In [1]: pd.read_sas?
Signature: pd.read_sas(filepath_or_buffer, format=None, index=None, encoding=None, chunksize=None, iterator=False)
Docstring:
Read SAS files stored as either XPORT or SAS7BDAT format files.

Parameters
----------
filepath_or_buffer : string or file-like object
    Path to the SAS file.
format : string {'xport', 'sas7bdat'} or None
    If None, file format is inferred.  If 'xport' or 'sas7bdat',
    uses the corresponding format.
index : identifier of index column, defaults to None
    Identifier of column that should be used as index of the DataFrame.
encoding : string, default is None
    Encoding for text data.  If None, text data are stored as raw bytes.
chunksize : int
    Read file `chunksize` lines at a time, returns iterator.
iterator : bool, defaults to False
    If True, returns an iterator for reading the file incrementally.

Returns
-------
DataFrame if iterator=False and chunksize=None, else SAS7BDATReader
or XportReader
File:      ~/pandas/pandas/io/sas/sasreader.py
Type:      function
```

```
In [2]: from pandas.io.sas import sas7bdat

In [3]: sas7bdat.SAS7BDATReader?
Init signature: sas7bdat.SAS7BDATReader(path_or_buf, index=None, convert_dates=True, blank_missing=True, chunksize=None, encoding=None, convert_text=True, convert_header_text=True)
Docstring:     
Read SAS files in SAS7BDAT format.

Parameters
----------
path_or_buf : path name or buffer
    Name of SAS file or file-like object pointing to SAS file
    contents.
index : column identifier, defaults to None
    Column to use as index.
convert_dates : boolean, defaults to True
    Attempt to convert dates to Pandas datetime values.  Note all
    SAS date formats are supported.
blank_missing : boolean, defaults to True
    Convert empty strings to missing values (SAS uses blanks to
    indicate missing character variables).
chunksize : int, defaults to None
    Return SAS7BDATReader object for iterations, returns chunks
    with given number of lines.
encoding : string, defaults to None
    String encoding.
convert_text : bool, defaults to True
    If False, text variables are left as raw bytes.
convert_header_text : bool, defaults to True
    If False, header text, including column names, are left as raw
    bytes.
File:           ~/pandas/pandas/io/sas/sas7bdat.py
Type:           type

```
we can start simple and expose ``convert_dates``
Looks like a typo : `Note all SAS date formats are supported`, could be `Not all SAS...`
> pandas/tests/io/sas/test_sas7bdat.py:test_productsales fails but it's not my issue, right?.)

The test is probably not valid any more.  If the test is not correct since it formerly returned floats and not returns datatime, then modify the test and add a comment with this issue number.
@bashtage updated `test_productsales` test along with its test data file
so this is a bug fix? IOW now it can parse dates in certain formats (correctly) that it couldn't before?

pls add a note in whatsnew/bug fixes/ IO section. Do users need to do anything differently? (e.g. ``convert_dates`` will just happen)?
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=h1) Report
> Merging [#15871](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/95f4f7dc78ac21a132b86b01c31efc5b0fdbceab?src=pr&el=desc) will **decrease** coverage by `0.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/15871/graphs/tree.svg?width=650&height=150&token=eZ4WkYLtcO&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #15871      +/-   ##
==========================================
- Coverage      91%   90.99%   -0.02%     
==========================================
  Files         162      162              
  Lines       49540    49547       +7     
==========================================
- Hits        45085    45083       -2     
- Misses       4455     4464       +9
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `88.77% <100%> (ø)` | :arrow_up: |
| #single | `40.25% <0%> (-0.07%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/sas/sas\_constants.py](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfY29uc3RhbnRzLnB5) | `100% <100%> (ø)` | :arrow_up: |
| [pandas/io/sas/sas7bdat.py](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXM3YmRhdC5weQ==) | `90.76% <100%> (+0.1%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.72% <0%> (-0.1%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=footer). Last update [95f4f7d...b6694cc](https://codecov.io/gh/pandas-dev/pandas/pull/15871?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

@jreback previously only one date format was supported (and no datetime formats). But there was a special note `Not all SAS date formats are supported`. So is it a bug then?.)
>Do users need to do anything differently? (e.g. convert_dates will just happen)?

`convert_dates=True` by default and it cannot be controlled in `read_sas` (as for now). So some columns treated earlier as numeric ones can become datetime columns.
> convert_dates=True by default and it cannot be controlled in read_sas (as for now). So some columns treated earlier as numeric ones can become datetime columns.

do we have an example of when this would happen?

What I am trying to understand, are SAS fields 'tagged' as datetime in any way? Or do we just try to convert all numeric columns???
> What I am trying to understand, are SAS fields 'tagged' as datetime in any way? Or do we just try to convert all numeric columns???

@jreback The fields are tagged, but I am not aware of a canonical listing of what values the tags are allowed to take.  This PR adds a bunch of tags to the list recognized as datetime/date/time columns.

> Do you have some more example files that we can add for testing? (IOW w/o some of these more common formats)?

I branched  [sas7bdat](https://bitbucket.org/jaredhobbs/sas7bdat) because I needed to speed up CSV conversion of some massive files.  If nothing else, the test cases I put together should all be applicable here.

@Winand A format that I recently encountered in the wild that I don't see in the new list is "YYMMDDN".  A date that _should_ be 2017-02-16 is showing up as 20866.0, so it looks like the format is "days elapsed since 1960-01-01".  Unfortunately I can't share the file this came from.
@jbrockmendel only date and datetime formats are supported. Time columns are left as is - numeric values. Afaik, there's no special dtype for time, is there?
UPD: hm.. seems that time columns use same raw numeric values as datetime (18:00==1960-01-01 18:00) So they can be treated as datetime. Maybe origin should be shifted to unix 1970 year in that case?
Here's an example: ![image](https://cloud.githubusercontent.com/assets/53390/24652043/cfafa97e-1938-11e7-8bf1-c8fc70b454be.png)

>A format that I recently encountered in the wild that I don't see in the new list is "YYMMDDN".

i've found a more complete list [here](https://github.com/epam/parso/blob/master/src/main/java/com/epam/parso/impl/SasFileConstants.java#L938)

>A date that should be 2017-02-16 is showing up as 20866.0, so it looks like the format is "days elapsed since 1960-01-01"

All dates in SAS are "days elapsed since 1960-01-01", date formats only indicate that a column is date column and how to display it in SAS. (there's also "informat" which makes things complicated.))

Have you tested out-of-range values?  In particular what happens to SAS values that are out of range for pandas `datetime`?  It is important to test cases just in, at and outside this boundary using SAS-produced binary files based on my experience with Stata.

@bashtage SAS supports dates 1jan1582---9999+. Such dates fail in `pd.to_datetime`. You are right, results at the boundary are quite interesting (see lines 3 and 5):
![image](https://cloud.githubusercontent.com/assets/53390/24656583/d0177a96-194b-11e7-905d-0669631d5ca2.png)

@Winand idealy if you can generate a file with dates per column in different formats (maybe the min/max dates accepted).
and a file of failing dates.

or @jbrockmendel maybe you could contribute such a file?

then we can actually say, hey these formats work, these don't.
@Winand 
> only date and datetime formats are supported. Time columns are left as is - numeric values. Afaik, there's no special dtype for time, is there?

My mistake.  I was thinking of the upstream version, which converts time columns to datetime.time objects.

@jreback 
I just looked quickly through my tests and don't see any datetime formats in the public-domain files.  Those came from [here](http://www.principlesofeconometrics.com/sas.htm) and [here](https://www.cdc.gov/nchs/tutorials/dietary/downloads/downloads.htm).  I'll take a closer look and see if there is anything else in here I can contribute.

Ideally the tests would use an intentionally generated file from SAS to catch all types covered and edge cases.
~~Oh. `The Taiwanese calendar uses 1912 as the base year`
http://support.sas.com/documentation/cdl/en/nlsref/69741/HTML/default/viewer.htm#n0a5psumjnpk92n16tiv1ilf3wcn.htm~~ Not an issue.
i've tested the boundary:
```
(sas float / sas date format > pd float / pd date)
to_datetime exceptions
    ^^
last bad: -8907752836.999999045 / 21SEP1677:00:12:43.000001907 > -8907752836.9999981 / 2262-04-11 23:47:16.709553616
last good: -8907752836.854775429 / 21SEP1677:00:12:43.145225525 > -8907752836.8547745 / 1677-09-21 00:12:43.145226
good: ...
last good: 9538991236.854775429 / 11APR2262:23:47:16.854774475 > 9538991236.8547745 / 2262-04-11 23:47:16.854774
last bad: 9538991236.999999045 / 11APR2262:23:47:16.999998093 > 9538991236.9999981 / 1677-09-21 00:12:43.290446384
    vv
to_datetime exceptions
```
When this is ready be sure to rebase/squash so that the binary files you aren't using don't end up in the main pandas git repo.

@Winand can you rebase / squash. Need a whatsnew note in 0.21.0 (Enhancements section)


 @bashtage can you have another look.

Done.
lgtm. @bashtage 

can you rebase and update?
@jreback yes, i'll try to update 'whatsnew' tomorrow.
~UPD: as soon as i remember how to resolve conflicts in SourceTree.)~ Done.