Using the SASHELP Library and the DICTIONARY Tables in DS2
DS2 does not support concatenated libraries. Since DS2 has been designed to be capable of running inside a database, such as Teradata, this is not an unreasonable restriction. However, it can be a problem when writing demonstration programs, because SASHELP, the source of so much useful demonstration data, is in fact a concatenated library.
There is DS2 syntax that will get around this restriction, by specifying a “connection” as an option to the Proc DS2 statement. The PRIMARYPATH location in the example below is typical. This then enables the SASHELP library to be used in the SET statement.
Although there is no observable difference in this case, it is important to realise that the query within the SET statement is written in FEDSQL, rather than the more familiar SQL dialect used by Proc SQL.
Although this workaround makes the tables of the SASHELP library available in DS2, it still does not make the SASHELP views available. Information of this kind, enabling DS2 programs to get details of the tables accessible to them, is available only via the DICTIONARY tables. Moreover, the DICTIONARY tables available in DS2 are not the ones we are familiar with from traditional data steps and Proc SQL; instead, they are the FEDSQL DICTIONARY tables, which are structured differently. One such table is called DICTIONARY.TABLES; the next piece of code uses it to discover the names of the others.
The query within the SET statement excludes all the standard map data sets, which are numerous. As with the previous example, this query is, formally speaking, written in FEDSQL, and TABLE_SCHEM is a column name specific to the FEDSQL table DICTIONARY.TABLES. (There is no column of this name in the table called DICTIONARY.TABLES which is accessible to Proc SQL.)
Having copied the interesting parts of DICTIONARY.TABLES into an ordinary SAS data set WORK.TABLIST, we can now examine that data set more easily, and see:
This lists the (only) five user-accessible FEDSQL DICTIONARY tables. TABLE_SCHEM is not the only unfamiliar column name here - the whole table structure is quite different from that of the "other” DICTIONARY.TABLES, the one accessible using Proc SQL. The structures of the other four FEDSQL DICTIONARY tables could be discovered in the way just used for DICTIONARY.TABLES, or – more conveniently – by looking in the Proc FEDSQL Reference Manual.