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
11 Comments
why not return the value as a string rather than global mVar?
Then you wouldn't need to return values in that %global
%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)); &engine %*put &libref. ENGINE is &engine.; %let rc= %sysfunc(close(&dsid.)); %end; %mend grtEngine; use it like %put library &lib uses engine %getEngine(&lib) ;but beware of concatenated libraries which could use more than one engine.
Peter, that's a great modification. There are probably several variations that would help to make this macro a better fit for certain purposes.
As far as the concatenated library with multiple engines, that's probably what we would call a "degenerate case", although I'm sure that some creative SAS users would be able to think of useful scenarios. It appears that SASHELP.VLIBNAM reports only a single engine for a concatenated library, even if the different library levels have different engines behind them. If you want to resolve the potential ambiguity for a known table, you can use the ATTRC function to fetch the library engine information as it applies to a given LIBREF.MEMBER value.
When I ran the macro as published, I got an error:
I changed the following line from:
to:
Also, the libref passed is case sensitive.
Alan, good catch! I fixed the example. I must have been getting lucky with the off-kilter parens.
You're correct: libref is case-sensitive, and library names in SASHELP.VLIBNAM will always be uppercase (as far as I know). Adjusting for that is one of the many possible improvements to this function. Keep in mind that using functions (such as UPCASE) within a query to SAS DICTIONARY tables (on which SASHELP.VLIBNAM is based) can scuttle the performance of the query, so it's best to resolve the case issues before supplying the value in a WHERE= option or clause.
Nice comment from Peter (above). Personally, I like this kind of 'tokenized' syntax for a macro call but there are some drawbacks :
1) code editors with syntax highlighting sometimes react badly with an implicit %put ¯oname inside a macro code , because unexpectedly the line doesn't end with a semi-colon
2) also since this syntax is not used very often - at least from my experience, developers are not so accustomed to it and the macro code could be difficult to maintain/understand at first sight.
I have run the code provided by Chris (thanks for sharing !) to see for myself how it worked with concatenated libraries since I didn't know several different engines could be concatenated.
I have come across a slight problem with a "i" format unknown to %sysfunc or even to myself !
And of course, as DICTIONARY views store values only in uppercase letters, I had to modify the first request like this :
this way, the parameter is not case-sensitive and you can use %getEngine(libRef) as well as %getEngine(LIBREF) by default.
I have assigned three 1st level libraries :
etc.
you can do the same by changing the order of the 1st level librefs in the assignment of the concatenated libraries : (I infer that ) the rule is that the 'compound' engine is the engine of the 1st libref which is the Output by order of precedence.
http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p0pynzuflex5orn1pkeycdeivuex.htm
The VLIBNAM/LIBRARIES retains the engine ot its first member in the case of a concatenated library and this means that it could retrieve data erroneously because it would apply this engine to the other members, wouldn't it ? I have missed something, to be sure.
Ronan
Ronan, good observations. Your experience with concatenated libraries is similar to mine. As I responded to Peter, I think it's a strange case but one that a robust macro function must consider.
I think you also encountered the misplaced parenthesis that I had in my original version -- another alert reader pointed that out and I fixed it.
As a more complete source for library engines where VLIBNAM might hide the engine of concatenation level=2+, perhaps the appropriate engine in a "multi-library-engine scenario" would only be revealed for particular members with the ATTRC() function. We can signal this in %getEngine without adding to the parameter list. When the parameter is just a LIBNAME, the current solution using SASHELP.VLIBNAM provides the answer, but when it is a LIBNAME.MEMNAME, then the ATTRC() function would be relevant.
So (with interesting results) I tried :
%macro getEngine( param )/des='library or member engine' ; %if %scan( &param, 2, . ) EQ %str() %then %do ; %let dsid = %sysfunc( open( sashelp.vlibnam( where=( libname EQ "%upcase(&param)" )), i )) ; %if &dsid %then %do ; %let engnum=%sysfunc( varnum( &dsid, ENGINE )) ; %let rc= %sysfunc( fetch( &dsid )) ; %sysfunc( getvarc( &dsid, &engnum )) %let rc= %sysfunc( close( &dsid )) ; %end ; %end ; %else %do ; %let dsid = %sysfunc( open( &param, i )) ; %if &dsid %then %do ; %sysfunc( attrc( &dsid, ENGINE )) %let rc= %sysfunc( close( &dsid )) ; %end ; %end ; %mend getEngine ; %LET LIB= SAShelp ; %put library &lib uses engine %getEngine(&lib) ; %put member vmacro uses engine %getEngine(sashelp.VMACRO) ;This revealed that a VIEW night have a different engine from the library.
There is another type of VIEW created in a data step, like:
data a dataA dataB /view=a; run; %put the engine for dataset dataA is %getEngine(work.dataA) ; %put the engine for a datastep view is %getEngine(work.A) ; %put the engine for dataset dataB is %getEngine(work.dataB) ;Only when the datastep VIEW was opened did the datasets defined on the DATA statement get created
I chose not to %UPCASE() the MEMNAME, because with SAS/Access engines, the value is preserved in many of the dictionary tables.
.... fun with SAS
Thanks Peter -- just another example of how one can tweak a macro on an ongoing basis to cover more and more scenarios!
Wouldn't that work as well:
%macro getEngine(libref); proc sql noprint; select max(engine) into :engine from SASHELP.VLIBNAM where upper(libname) = upper("&libref.") ; quit; %put &libref. ENGINE is &engine.; %mend;Martin,
Yes, that version works. In fact, that was the first version my colleague had come up with. However, when used within a macro program you can't always submit PROC or DATA steps inline without restricting how the macro operates. The version that I posted (as well as some of the good suggestions within the other comments) should work from any context, regardless of where you use it.
I'm Chris's colleague that was trying to figure this out originally :-). The main reason that I didn't want to use a proc or data step was because I wanted to use the macro as a function as Peter described. And the reason that I need this macro in the first place is because we can have data stored in either SPDS or Hadoop, and there are limitations about tables in Hadoop that are not there in SPDS (primarily that you can't apply an index to a table stored in Hadoop through SAS/Access), so we need to behave differently for the two storage methods. Here is my final version of the getEngine macro (in case anyone cares):
/* ****************************************************************** */ /* SAS Macro: getEngine */ /* ------------------------------------------------------------------ */ /* Description: */ /* Given a libref as input, returns the engine of the library. */ /* This is a macro function, so it should be used in a format similar */ /* to these examples: */ /* In a data step: */ /* engine = "%getEngine(sashelp)"; */ /* In a %let: */ /* %let engine = %getEngine(sashelp); */ /* In a %if: */ /* %if (%getEngine(sashelp) eq V9) %then */ /* ------------------------------------------------------------------ */ /* Parameters: */ /* libref - libref of the library. If the libref is blank or */ /* unassigned, then a blank value will be returned from */ /* the macro. */ /* ------------------------------------------------------------------ */ /* History */ /* 04JUN2013 1.0 Production version */ /* ------------------------------------------------------------------ */ /* Copyright (c) 2013 SAS Institute, Inc Cary, NC USA */ /* All rights reserved */ /**********************************************************************/ %macro getEngine(libref); %let _engine =; /* in case the parameter is a libref.tablename, pull */ /* off just the libref */ %let libref = %upcase(%scan(&libref, 1, %str(.))); /* open the vlibnam view */ %let _dsid = %sysfunc(open(sashelp.vlibnam(where=(libname="&libref")))); /* if the view opened successfully then fetch the first record */ /* and get the value of the engine variable */ %if (&_dsid gt 0) %then %do; %let _rc = %sysfunc(fetch(&_dsid)); %if (&_rc eq 0) %then %do; %let _engineVarnum = %sysfunc(varnum(&_dsid, ENGINE)); %let _engine = %sysfunc(getvarc(&_dsid, &_engineVarnum)); %end; /* if - fetch was successful */ %let _dsid = %sysfunc(close(&_dsid)); %end; /* if - view opened */ /* return engine value to calling program */ &_engine %mend getEngine;