Data set options on Dictionary table

[Subbarao]

Hi,

Can't i apply dataset options on Dictionary tables in PROC SQL? Check the below code.

PROC SQL ;

CREATE TABLE TEST AS

SELECT * FROM DICTIONARY.TABLES(WHERE=(NOBS > 3090));

QUIT;

[Kurt_Bremser]

Use the SQL where:

PROC SQL ;

CREATE TABLE TEST AS

SELECT * FROM DICTIONARY.TABLES WHERE NOBS > 3090;

QUIT;

Data set options do not work on these "virtual" tables only available in proc sql;

[Subbarao]

Thank you. But Below code works fine.

PROC SQL;

CREATE TABLE TEMP AS

SELECT * FROM SASHELP.BWEIGHT(WHERE=(WEIGHT>3060));

QUIT;

Is it that, we can't apply data set options on Dictionary tables?

I found a SUGI paper on this

http://www2.sas.com/proceedings/sugi31/131-31.pdf

[Kurt_Bremser]

As I said, these tables do not actually exist as tables (you will not find a library "DICTIONARY" in your server list or in the SAS Explorer), but are more or less created "on the fly" when accessed. Just watch the time needed to skim through just a few thousand records.

The section "Limitations" in the pdf has a specific line about this.

[Subbarao · Accepted solution]

Per link below, its stated that we cannot use dataset options in for Dictionary tables in PROC SQL.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001385596.htm

Note:   You cannot use data set options with DICTIONARY tables.

[RW9]

You could replace dictionary.tables with sashelp.vtable of course.

From your code I am not entirely sure why there is a need to use dataset options in such a way.  Most people will recognize basic SQL, so why go to all that effort to obscure what you are doing?