02-08-2014 06:35 AM
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 ???
02-08-2014 09:39 AM
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,
02-08-2014 11:43 AM
If you DESCRIBE the view in proc sql it will report the underlying dictionary table
describe view Sashelp.Vmember ;
That revealed it reports dictionary.members
2 proc sql;
3 describe view Sashelp.Vmember ;
NOTE: SQL view SASHELP.VMEMBER is defined as:
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
02-08-2014 11:46 AM
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