Read SAS, Stata and SPSS files directly in SQL, and write query results to Stata, SPSS and SAS files.
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|
| Linux AMD64 | |||
| Linux ARM64 | |||
| macOS AMD64 | |||
| macOS ARM64 | |||
| Windows AMD64 | |||
| Windows ARM64 | |||
| WebAssembly |
Installing and Loading
INSTALL read_prs FROM community;
LOAD read_prs;
Example
INSTALL read_prs FROM community;
LOAD read_prs;
-- Write a small Stata file, then read it back
COPY (SELECT 1 AS id, 'Ada' AS name, DATE '1815-12-10' AS born)
TO 'read_prs_example.dta' (FORMAT dta);
SELECT * FROM read_prs('read_prs_example.dta');
-- id | name | born
-- 1 | Ada | 1815-12-10
-- Only the columns a query uses are read from the file
SELECT name FROM read_prs('read_prs_example.dta') WHERE id = 1;
About read_prs
read_prs(path) is a table function that reads SAS (.sas7bdat), Stata (.dta) and SPSS
(.sav, .zsav) files, and the less common SAS transport (.xpt) and SPSS portable (.por)
formats. The format is taken from the file extension.
SELECT age, income FROM read_prs('survey.sav') WHERE age >= 18;
CREATE TABLE survey AS SELECT * FROM read_prs('survey.sas7bdat');
- Only the columns a query uses are read from the file.
- Files are opened through DuckDB's file system, so
s3://paths use your DuckDB secrets and settings. - Options follow polars_readstat:
preserve_order,row_index_name,value_labels_as_strings,missing_string_as_null,row_offset,n_rows,threadsandbatch_size. -
COPY ... TOwrites any query result as Stata (FORMAT dta), SPSS (FORMAT sav), a CSV plus a SAS script that loads it (FORMAT sas_csv), SAS transport (FORMAT xpt) or SPSS portable (FORMAT por), with variable labels.
It reads files with the same Rust core as polars_readstat. Documentation: https://github.com/jrothbaum/duckdb_prs
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| read_prs | table | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |