Proc SQL can be used to query an SQLite (or other) database. Output from the Proc SQL query is stored as a file which can then either be viewed using Excel, or can be the basis for further analysis. SQLite is an open source, public domain database system, and is also SQL92 compliant. SQLite can be an effective part of any auditor's software toolkit. Further information as to its practicality for audit analysis can be obtained at the SQLite organization web page.

Extract SQLite Data to Excel

Internal auditors are increasingly using database software to support their analytical work. Often, data can be stored in the SQLite database, and then query results extracted and further analyzed using Excel. Below is an example of an SQLite query using Proc SQL whose results can be further analyzed using Excel:

Auditors wishing to use Proc SQL may invoke queries using the following syntax:

Proc SQL Syntax

proc SQL data=DATAFILE db=DBNAME;
connect CONNECT;
SQL;

Proc SQL Parameters Used

There are four primary parameters for the SQL select:
DATAFILE - where the query results will be stored
CONNECT - the name of the mysql connection defined in the Script basic INI file
DBNAME - always SQLITE when running a select against an SQLITE database
SQL - the text of the SQL select command

Proc SQL Example Script

*;
* sqlitesel1.ezs
* SQLite selection function using proc sql
* process an SQLite SQL query using proc sql;
*;
libname drug 'c:\test\model';
libname test 'c:\test\model\tested';
proc sql db=SQLITE data=test.h3;
connect 'C:\test\ccode\cLoadSQLite\test.db';
Select count(*) ccount
from routes
;

The Proc SQL procedure is part of EZ-R Stats for Windows.

Analysts interested in Proc SQL may also be interested in Proc Means, Proc Outlier, Proc Benford and Proc Univariate. A description of audit uses for SQLite is available for download. Further examples of SQL are available for the National Drug Codes.