Skip to content
/ sas2db Public

Import SAS files to SQL databases

License

Notifications You must be signed in to change notification settings

XDgov/sas2db

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

54 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

sas2db PyPI version

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:

Information about data types: