topic Re: Export SAS table to sqlite3 in SAS Enterprise Guide

Export SAS table to sqlite3

vietlinh12hoa — Thu, 24 Jun 2021 11:21:36 GMT

I have a SAS table with 100 million rows, it's not efficient to export under csv format. Any chance sas can convert the table into sqlite3 (python) to be opened in Python?

Re: Export SAS table to sqlite3

Kurt_Bremser — Thu, 24 Jun 2021 11:37:04 GMT

I don't think that SAS provides a module for that particular format.

But you can compress the csv on the fly to reduce space:

filename class zip "/folders/myfolders/class.zip";

data _null_;
set sashelp.class;
file class(class.csv) dlm="," dsd;
if _n_ = 1 then put "name,sex,age,height,weight";
put name sex age height weight;
run;

filename class clear;

You will need to unpack it on the target server before reading it into the database.

Re: Export SAS table to sqlite3

Patrick — Thu, 24 Jun 2021 11:46:18 GMT

Googling using site:documentation.sas.com sqlite returns a few hits. Not sure if any will be helpful to you but suggest you find out for yourself.

Re: Export SAS table to sqlite3

Sajid01 — Thu, 24 Jun 2021 13:04:58 GMT

SQLITE supports odbc . You can download the driver from http://www.ch-werner.de/sqliteodbc/ .
The documentation is here http://www.ch-werner.de/sqliteodbc/html/index.html .
Please do update if it has worked for you so that others may benefit.

Re: Export SAS table to sqlite3

AlanC — Thu, 24 Jun 2021 14:19:37 GMT

libname sqlite odbc complete="dsn=SQLite3 Datasource;Driver={SQLITE3 ODBC Driver};Database=e:\temp\IISHistoricalSample.db";

libname temp 'e:\temp' ;

data sqlite.Sample;

set temp.All_iis_new (obs=100);

run;

Re: Export SAS table to sqlite3

AlanC — Thu, 24 Jun 2021 14:21:55 GMT

Also, Sqlite is not related to Python. It is a nice system because it is a single file database. For SAS data, if I recall correctly, I get an enormous savings in space vs a SAS dataset. It has its issues such as minimal typing (ex. no datetime).

Re: Export SAS table to sqlite3

SASKiwi — Fri, 25 Jun 2021 00:47:17 GMT

You'll need SAS's SAS/ACCESS to ODBC product for @AlanC 's approach.