sas2db 0.2.1

Import SAS files to SQL databases

sas2db

Imports SAS files to SQL databases. Supports both *.sas7bdat and XPORT (*.xpt) files.

Installation

The SAS software itself is not required.

1. Install Python 3 and pip.

2. Install the package.

   pip3 install sas2db
   

Usage

SQLite3

sas2db supports SQLite3 with no additional dependencies or setup, so we'll start with that. To import from SAS to SQLite3:

1. Run the conversion.

   sas2db path/to/src.sas7bdat
   

2. A src.db (matching your input file name) will be created.

3. Run SQL! Example:

   $ sqlite3 src.db
   sqlite> .tables
   mydata
   sqlite> SELECT COUNT(*) FROM mydata;
   200
   

For more options:

sas2db -h

Other databases

Aside from SQLite3, sas2db supports other databases like PostgreSQL and MySQL. This support comes from SQLAlchemy under the hood, so see their list of supported "dialects".

To use another database:

1. Ensure that the destination database is installed, running, created, and accessible from wherever you will be doing the import.
2. Install the corresponding driver.
   • On the Dialects page, click your preferred database, then under "DBAPI Support", click one of the options.
   • The first DBAPI option is probably fine, though you may have to try multiple.
3. Run sas2db, passing the database URL to the --db argument.

Example for PostgreSQL:

# create the database
createdb -U postgres sas_import
# install driver
pip3 install psycopg2
# run the import
sas2db --db postgresql+psycopg2://postgres@localhost:5432/sas_import path/to/src.sas7bdat

Development

1. Install Pipenv.

2. Clone/download the repository.

3. From the project directory, run

   pipenv install --dev
   pipenv shell
   

4. Run PostgreSQL. Example in Docker:

   docker run --rm -it -p 5432:5432 --name pg postgres
   

5. Create sas2db database in PostgreSQL for testing. Example in Docker:

   docker exec -it pg createdb -U postgres sas2db
   

6. Run tests:

   python -m unittest
   

To use the script:

python3 sas2db/run.py path/to/src.sas7bdat

data.gov has data sets you can test with.

More Information

Other tools to look at for importing/exporting data to/from SAS:

• sas7bdat Python package
  • Useful in case you can't use Pandas for some reason
  • Pandas (used in sas2db) uses a modified version of the sas7bdat package
• SAS ODBC drivers
• SAS's PROC EXPORT
• sas2bdat R package

Information about data types:

• Documentation
  • SQLite types
  • SAS data types
  • SAS formats
• SAS to Python type parsing
  • sas7bdat
  • Pandas