topic Re: Data set options on Dictionary table in SAS Programming

Data set options on Dictionary table

Subbarao — Fri, 13 Feb 2015 12:49:53 GMT

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;

Re: Data set options on Dictionary table

Kurt_Bremser — Fri, 13 Feb 2015 13:01:58 GMT

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;

Re: Data set options on Dictionary table

Subbarao — Mon, 16 Feb 2015 08:22:54 GMT

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

Re: Data set options on Dictionary table

Kurt_Bremser — Mon, 16 Feb 2015 08:53:41 GMT

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.

Re: Data set options on Dictionary table

Subbarao — Mon, 16 Feb 2015 09:16:28 GMT

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.

Re: Data set options on Dictionary table

RW9 — Mon, 16 Feb 2015 09:30:35 GMT

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?