Undocumented and Hard-to-find PROC SQL Features

The SQL Procedure contains many powerful and elegant language features for advanced SQL users. This paper presents SQL topics that will help programmers unlock the many hidden features, options, and other hard-to-find gems found in the SQL universe. Topics include CASE logic; the COALESCE function; SQL statement options _METHOD, _TREE, and other useful options; dictionary tables; automatic macro variables; and performance issues. Introduction This paper illustrates several undocumented and hard-to-find PROC SQL features . Although undocumented options may be freely explored and used, users are cautioned about using them before considering their ramifications. Because of unannounced changes, possible removal, or not being supported in future releases; a certain amount of care should be exercised before using them throughout all SQL procedure applications. With this stated, undocumented options provide a wealth of opportunities for identifying and resolving coding problems. This paper also addresses several hard-to-find PROC SQL features. Unlike undocumented features, these features may not always be extensively written on; they are nonetheless classified as production-level features and are not only supported in current releases , they should be supported well into the future. Example Tables The data used in all the examples in this paper consists of a selection of movies that I’ve viewed over the years. The Movies table consists of six columns: title, length, category, year, studio, and rating. Title, category, studio, and rating are defined as character columns with length and year being defined as numeric columns. The data stored in the Movies table is depicted below.