Wednesday, 28 October 2009

NOTE: Disappearing Tables and Secret Syntax

A client called me today with a very strange problem. They were creating work data sets named work._tor1 and work._tor2. The code was working but they couldn't see the tables in Enterprise Guide's Server List window. Were these tables being cleaned-up automatically they asked me. A bit of internet research revealed that data sets (in any library) whose names begin "_TO" are hidden by SAS because SAS's own temporary data sets are named in this fashion. So, the data sets exist, but they're not visible to the user in EG's Server List pane and in Display Manager's SAS Explorer pane.

I got confirmation of this from Wei Cheng's intriguing SUGI 29 paper entitled "Helpful Undocumented Features in SAS". The paper is a goldmine of information. I strongly caution against using any of these features in a production system, but a number of them are very useful during development, testing or debugging. These are my favourites...


Whilst many NOTE: readers will already know this, it's always worth repeating for the benefit of those who don't. If you have a complex join in your SQL, adding the _METHOD  keyword to the PROC SQL statement will give you a listing of the order in which the joins will be evaluated. The information uses codes that Wei lists in the paper. Adding the _TREE keyword to the PROC SQL statement provides further insight into how SQL is evaulating your joins. Wei shows some example code and some example output. Highly recommended.

Groups of Options

Running PROC OPTIONS to list-off your option settings is great but tends to fill-up the log. On the other hand, I may want to see more than one option value, so using OPTION= has limited appeal. Wei points-out that we can use the GROUP= parameter. If this was undocumented when Wei wrote the paper, it's not any more and you can see the list of valid values for GROUP in the "Displaying the Settings of a Group of Options" section in the online help. Groups include PERFORMANCE, ERRORHANDLING and MACRO.

List of Engines

Perhaps of limited use, but of some curiosity, PROC NICKNAME lists all of the engines available to you. The list is quite long. Each engine is annotated with a variety of descriptive information (see Wei's paper for an example).