Data set options on Dictionary table

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Data set options on Dictionary table

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;


Accepted Solutions
Solution
‎02-16-2015 04:16 AM
Contributor
Posts: 39

Re: Data set options on Dictionary table

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.

View solution in original post


All Replies
Super User
Posts: 7,762

Re: Data set options on Dictionary table

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 39

Re: Data set options on Dictionary table

Posted in reply to KurtBremser

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

Super User
Posts: 7,762

Re: Data set options on Dictionary table

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎02-16-2015 04:16 AM
Contributor
Posts: 39

Re: Data set options on Dictionary table

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.

Super User
Super User
Posts: 7,942

Re: Data set options on Dictionary table

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?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 251 views
  • 0 likes
  • 3 in conversation