topic Re: Dictionary Tables in SAS Programming

Dictionary Tables

sumitpratap — Sat, 08 Feb 2014 11:35:49 GMT

Hello, I can see sashelp views corresponding to dictionay tables.But I don't know where to find Dictionary tables.

I want to know the exact path of Dictionary tables.I want to browse then as a can with VTABLE.

I want to see then through explorer.

Can anyone help me out ???

Re: Dictionary Tables

Haikuo — Sat, 08 Feb 2014 14:39:47 GMT

you are talking about the SAME thing with different angles. SAS dictionary tables are metadata VIEWS that you can access using two ways, which you already know.

1. sashelp V tables. From this angle you can browse it using SAS explore window.

2. Proc SQL Dictionary libname. This libname is NOT a regular libname, it is longer than 8 character and you can't find it using explore window. I mainly use this approach when doing subset involving WHERE clause, it is much faster than SASHELP VIEW. I forgot but there is a reason for that recorded in SAS doc, maybe someone can dig out for you.

Hope this can help to clear up your doubts,

Haikuo

Re: Dictionary Tables

Peter_C — Sat, 08 Feb 2014 16:43:37 GMT

If you DESCRIBE the view in proc sql it will report the underlying dictionary table

proc sql;

describe view Sashelp.Vmember ;

quit ;

That revealed it reports dictionary.members

2 proc sql;

3 describe view Sashelp.Vmember ;

NOTE: SQL view SASHELP.VMEMBER is defined as:

select *

from DICTIONARY.MEMBERS;

4 quit ;

NOTE: PROCEDURE SQL used

So describe the underlying table:

8 proc sql ;

9 describe table dictionary.members ;

NOTE: SQL table DICTIONARY.MEMBERS was created like:

create table DICTIONARY.MEMBERS

(

libname char(8) label='Library Name',

memname char(32) label='Member Name',

memtype char(8) label='Member Type',

dbms_memtype char(32) label='DBMS Member Type',

engine char(8) label='Engine Name',

index char(3) label='Indexes',

path char(1024) label='Pathname'

);

10 quit ;

NOTE: PROCEDURE SQL used

Re: Dictionary Tables

Paul_Kent_SAS — Sat, 08 Feb 2014 16:46:06 GMT

the "tables" inside DICTIONARY dont exist as SAS datasets anywhere. they are "created" in real time as you reference them. presenting the information in a tabular format is useful for many reasons, and many database systems have these "schema information tables".

so for example, when you query DICTIONARY.LIBNAMES, the SQL software asks the SAS supervisor for a list of libnames defined "at this moment", and packages up the answer as if it really were a table.

thats why using a where clause can be important. select * from DICTIONARY.COLUMNS; means opening every dataset, in every visible libname, to inspect its column names - these are not stored centrally with SAS, they are stored distributed in each dataset. WHERE libname = 'FRED' allows us to skip all the other libraries

Re: Dictionary Tables

sumitpratap — Sun, 09 Feb 2014 06:50:47 GMT

Thanks Paul...this has really helped me...