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;
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.
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;
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
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.
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.