BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Subbarao
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Subbarao
Fluorite | Level 6

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

5 REPLIES 5
Kurt_Bremser
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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?

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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