ENH: SAS7BDAT reader
State: closed

This needs more testing, but basically seems to work for reading uncompressed SAS7BDAT files.  I will add support for compression in a few days.

closes #4052 


Labels:
name: Enhancement
name: IO SAS
description: SAS: read_sas

Comments:
nice! I would put this in a subdir

`io/sas/...`

Mislabeled as IO Stata, should be IO SAS

@kshedden right!

hows this going?

Wow! I need this feature very much.) Is it written completely from scratch or using https://bitbucket.org/jaredhobbs/sas7bdat source? I've got a huge performance improvement with the latter moving the most frequently called parts into Cython module

@Winand thanks for the feedback.  This is heavily based on Jared Hobbs' code.  The decompressors were totally rewritten in cython partly for performance and partly because there are known issues with the existing implementations.  I need to do more testing before this is ready to merge.

@kshedden i hope it'll be fast, i need to read 1-1.5M rows table.
Please test codepage 1251 support before merge:-D

I wrote this because I need to read 500M row files.  Current timing on a pretty fast server is 11 seconds per 100,000 rows (with 28 columns, a few needing datetime conversion).  Could be faster I'm sure.  I do need to test encodings.

Getting closer, still to do:
- [x] Try to find encoding information in SAS7bdat file
- [x] Move `_process_byte_array_with_data` into the cython file, rename it something more appropriate

does the SAS file actually have what encoding its in (in the file itself)? ususally this is an external parameter as you need to decode before reading :) though its in binary so I suppose it could be (e.g. self descripting)

Yes, the encoding is in the SAS log and in the output of proc contents.  And you can specify it when creating a dataset.  So I think it must be explicitly in there somewhere.  But its offset position is not stated in any of the documents I have seen. 

How are you going to read 500M rows? This would need MUCH RAM. My current workflow: iteratively read 1.5M rows in 200k chunks (18 columns), save chunks to an on-disk storage (homemade, Castra-like) with Categoricals. Then read from storage to a DataFrame.

---

Trying to make Hobbs' code faster i've found out that handling codecs individually is better than `s.decode('codec_name')` (actually i handle cp1251 only)

```
if encoding_is_cp1251:
    row_elements.append(codecs.charmap_decode(s,encoding_errors,encodings.cp1251.decoding_table)[0])
else: ...
```

@jreback, I don't have any more outstanding things to do here.  If you have any comments let me know.

@kshedden can you add an asv for this (IIRC there is one for the xport already), you can use a test file in the repo

I tried to write an asv, but I can't run it because I don't have access to a machine with conda and I can't get asv to work with virtualenv.

Also, I haven't included my asv benchmarks in the PR because the gitignore excludes the asv directory.  Am I supposed to override this somehow?

We don't have xport benchmarks for these same reason, but I can add one if we can get over these obstacles.

the `asv_bench` dir is excluded, so you have to `-f` add them. The issue IIRC was that a lot of extraneous things were getting added (e.g. you can make .asv generate html output and its very easy to accidently add them). should just change it to add only .py I think by default. Anyhow can do that in another PR.

looks pretty good. small comments, pls rebase.
- `io.rst` needs updating
- whatsnew note (could be a separate sub-section in enhancements)

I don't think this is an API change for xportreader in any event, right?

@kshedden looks good! just some minor doc comments.

you can squash or I can do on merge.

ping when green.

this will close #4052, though that has a `to_sas`. does anything like that exist? is it even worth it?

(IMHO) as i understand sas7bdat is SAS internal format, it's not widely used to share data. It will require a lot of effort to create sas7bdat writer, but it will be rarely used

@Winand that's what I thought. once you go pandas you don't go back :smile: 

@kshedden tests look good. just need some pep cleanup (you can use `autopep8` if you want), or fix manually

FYI, see https://github.com/pydata/pandas/commit/6100a769ebd4ad9129d09feb16029be283bbccd7

I had to rename existing .XPT and .DTA to lowercase (as they weren't installing correctly) and didn't want a mix of things.

Am I supposed to rebase this PR on 6100a76?  I am getting some rename conflicts that I can't figure out how to resolve.

yes 

I renamed some XPT to xpt and DTA to dta 

@kshedden so we have the lint filters turned on now- you can see the errors at the bottom of the travis output

and locally

`flake8 pandas io/sas`

@kshedden if you could update would be great

@jreback can you give me a tip about these test failures?

pep issues

```
pandas/io/sas/__init__.py:2:1: F403 'from pandas.io.sas.api import *' used; unable to detect undefined names
pandas/io/sas/api.py:1:1: F401 'read_sas' imported but unused
```

the other failure I will fix in a moment (xarray updated to a new version)

@kshedden ok, rebase away and you should be good