How to find the ENGINE name for a SAS library

One of the great things about SAS libraries is that you can write your programs to read and write data without having to worry about where the data lives. SAS data set on a file system? Oracle table in a database server? Hadoop data in Hive? For many SAS applications, the programmer can treat these very different data sources in exactly the same way.

…except for the cases where you can’t treat them the same, because they are different. These different databases have different capabilities and behaviors, and sometimes you need to optimize your SAS programs to take advantage of those differences.

Recently, a colleague at SAS needed a reliable SAS macro-based method to detect the library engine for a given libref. Here’s what we came up with:

%macro getEngine(libref);
 %global ENGINE;
 %let dsid=%sysfunc(open(sashelp.vlibnam(where=(libname="&libref."))),i);
 %if (&dsid ^= 0) %then %do;  
   %let engnum=%sysfunc(varnum(&dsid,ENGINE));
   %let rc=%sysfunc(fetch(&dsid));
   %let engine=%sysfunc(getvarc(&dsid,&engnum));
       %put &libref. ENGINE is &engine.;
   %let rc= %sysfunc(close(&dsid.));
 %end;
%mend;

This simple macro peeks inside SASHELP.VLIBNAM, a virtual table that maintains the vital characteristics of all of the active SAS libraries in a session. The macro queries the table for the engine name for the given library, and places the result in a macro variable named &ENGINE.

Here are some example uses and results. The first two calls are for built-in SAS libraries, which use the BASE engine (aliased to “V9”). The third call is for a MySQL library that I use for reporting on our WordPress database.

34         %getEngine(SASHELP);
SASHELP ENGINE is V9
35         %getEngine(WORK);
WORK ENGINE is V9
36         %getEngine(WPBLOGS);
WPBLOGS ENGINE is MYSQL