Export SAS table to sqlite3

[vietlinh12hoa]

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?

[Kurt_Bremser]

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.

[Patrick]

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.

[Sajid01]

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.

[AlanC]

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;

[AlanC]

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).

[SASKiwi]

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