BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LoriGoldman
Obsidian | Level 7

Is there a way to read a SQLite database using SAS?  Is there an ACCESS Interface available?  There is an R application that writes to a SQLite database and I'm trying to figure out a way to read the data generated in the app.  Any ideas?  I'm running SAS 9.3.2 in Windows 7.  We have SAS Access for ODBC.  With the right driver, can it be used to read SQLite?

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

as you can  access SQLite and having sas/access that should be able to be used.

SQLite ODBC Driver (www.ch-werner.de/sqliteodbc) take care of the bitness. With a 32-bit SAS you are needing a 32-bit driver with a 64-bit SAS the 64-bit driver.

If you have IML you could connect that one to R. 

---->-- ja karman --<-----

View solution in original post

4 REPLIES 4
jakarman
Barite | Level 11

as you can  access SQLite and having sas/access that should be able to be used.

SQLite ODBC Driver (www.ch-werner.de/sqliteodbc) take care of the bitness. With a 32-bit SAS you are needing a 32-bit driver with a 64-bit SAS the 64-bit driver.

If you have IML you could connect that one to R. 

---->-- ja karman --<-----
Reeza
Super User

Yes, simply download the SQLITE ODBC driver as indicated by

THere are 3 types though, besides bitness, so you'll need to know if the database was created as SQLITE 3 or another source. Or try each one 🙂 until one works.

You probably need admin privileges on your computer to accomplish this.

StatNoodle
Calcite | Level 5

Funny you should ask.  I am working on a SQL Pass-through engine for SQLite v3 right now. I am recruiting anyone interested in beta-testing the engine.  If you are interested, reply.  The engine does not require SAS/ACCESS for ODBC, etc.  This is an internal SAS pass-through engine (see below for the LIBNAME engine, information).

Current status on SQLite Pass-Through engine (SQLitePT):

  • The engine connects/disconnects with the SQLITE3.DLL.
  • The engine opens/closes the file you tell it to with ACCESS=READONLY | READWRITE | CREATE.
  • The engine executes SELECT queries but currently returns everything as a 128 byte character string (see below).
  • The engine can perform an EXECUTE (sqlite-sql-code) BY command.
  • The EXECUTE () BY also includes an option to return results to the LOG or not.  This is needed because of SQLite's love of PRAGMA commands to retrieve information and the SQL-Pass-Through requirement that information may only be retrieved from the DB via a SELECT query.  EXECUTE has no facility to return information to SAS (except via LOG or OUTPUT).  I will get around this later by exploiting commented hints in the query.
  • Currently working on data type evaluation which is tricky in SQLite since the engine does not really type columns, at all.  This will follow three methods: Hints, Declared types, and Guess (sample with type promotion, sample with type TEXT, sample with type MAJORITY).
  • The options parser needs work -- I thought I would not need so many options but they keep spiraling and my simple state-machine is becoming cumbersome.

The Pass-through engine was my first effort because it is relatively simple [except for data typing, the SQLitePT engine is almost a direct mapping between the SAS Pass-through API requests and SQLite API fulfiller].  This is my introduction to the SQLite API.

It will be followed by a SQLite LIBNAME engine later this year or early next year.  This is partially written but nowhere near beta-testing.  Most likely January before a beta version would be available since I have a day-job and this ain't it :smileygrin: -- this is just promotion-ware).

I'm toying with a Cassandra interface.

whiteray
Calcite | Level 5

Dear Ms/Mr Noodle,

 

We are interested in your "SQL Pass-through engine for SQLite v3".

 

We are running Red Hat Linux on our server (2.6.32-431.20.3.el6.x86_64).  Would that be compatible with your DLL?

 

Thanks.

 

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 12808 views
  • 1 like
  • 5 in conversation