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?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.