Sample 25436: Demonstrates basic SQL DICTIONARY statements
 |  |  |  |  |
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This sample is from the SAS Sample Library. For additional information refer to SAS Help and Online Documentation.
/***************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: SQLDICT */
/* TITLE: Demonstrates Basic SQL Dictionary Statements */
/* PRODUCT: SAS */
/* SYSTEM: ALL */
/* KEYS: DATA MANAGEMENT, */
/* PROCS: SQL */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: */
/* */
/***************************************************************/
/* The first step is to create the Paper table which will */
/* be used in the following queries. */
data paper;
input author$1-8 section$9-16 title$17-43 @45 time time5.
duration;
format time time5.;
label title='Paper Title';
cards;
Tom Testing Automated Product Testing 9:00 35
Jerry Testing Involving Users 9:50 30
Nick Testing Plan to test, test to plan 10:30 20
Peter Info SysArtificial Intelligence 9:30 45
Paul Info SysQuery Languages 10:30 40
Lewis Info SysQuery Optimisers 15:30 25
Jonas Users Starting a Local User Group 14:30 35
Jim Users Keeping power users happy 15:15 20
Janet Users Keeping everyone informed 15:45 30
Marti GraphicsMulti-dimensional graphics 16:30 35
Marge GraphicsMake your own point! 15:10 35
Mike GraphicsMaking do without color 15:50 15
Jane GraphicsPrimary colors, use em! 16:15 25
;
proc sql flow=25;
/* Show me what information is available thru the */
/* SQL DICTIONARY tables. */
title2 'Description of each DICTIONARY table';
describe table dictionary.members,
dictionary.tables,
dictionary.catalogs,
dictionary.columns,
dictionary.extfiles,
dictionary.indexes,
dictionary.options,
dictionary.views;
/* Create indexes over the author column, PAPER table */
create index author on paper (author);
create unique index ast on paper (author, section, title);
/* Where does my new TABLE reside? */
title2 'Where does my new table, PAPER, reside?';
title3 'dictionary.members';
select *
from dictionary.members
where memname = 'PAPER';
/* When was the PAPER table created? */
title2 'When was the table created?';
title3 'dictionary.tables';
select libname, memname, crdate, modate
from dictionary.tables
where memname = 'PAPER';
/* Tell me about the table... */
/* How many variables are there? */
/* How many observations are there? */
/* How long are my observations? */
/* Is the table password protected? */
/* Is the table compressed? */
title2 'Tell me more about the PAPER table.';
title3 'dictionary.tables';
select memname, nvar, nobs, obslen, protect, encrypt, compress
from dictionary.tables
where libname = 'WORK' and memname = 'PAPER';
delete *
from work.paper
where author = 'Mike';
title2 'How many observations have been deleted';
title3 'dictionary.tables';
select memname, nobs, delobs
from dictionary.tables
where memname = 'PAPER';
/* What information about catalogs will we find? */
title2 'What Catalog information is available?';
title3 'dictionary.catalogs';
select memname, memtype, objname, objtype, objdesc, alias
from dictionary.catalogs
where libname = 'SASHELP' and memname like 'BASE%' and
objtype = 'HELP';
/* When was the catalog created or last modified? */
title2 'When was the Catalog created or last modified?';
title3 'dictionary.catalogs';
select libname, memname, objname, objtype, modified, created
from dictionary.catalogs
where libname = 'SASHELP' and memname like 'BASE%' and
objtype = 'HELP';
/* Tell me column information for the PAPER table? */
title2 'Do the columns have formats, informats, or labels?';
title3 'dictionary.columns';
select name, label, format, informat
from dictionary.columns
where memname = 'PAPER';
title2 'Show me column position in PAPER table';
title3 'dictionary.columns';
select name, npos, varnum
from dictionary.columns
where memname = 'PAPER';
/* Does my PAPER table have indexes? */
title2 'Index information for PAPER table';
title3 'dictionary.indexes';
select name, idxusage, indxname, indxpos, unique
from dictionary.indexes
where libname = 'WORK' and memname = 'PAPER';
/* What's my current page and line size? */
title2 'What are my current page and line size options set to?';
title3 'dictionary.options';
select optname, setting, optdesc, level
from dictionary.options
where optname in ('PAGESIZE', 'LINESIZE');
/* Let's make only the afternoon presentations available */
create view pm as
select * from paper
where time > '12:00't;
/* How about the presentations that last 30 minutes or less */
create view halfhr as
select * from paper
where duration <= 30;
/* Where do my new VIEWS reside? */
title2 'Tell me where my VIEWS reside?';
title3 'dictionary.views';
select *
from dictionary.views
where memname in ('PM', 'HALFHR');
title2 'View information is stored in dictionary.members too!';
title3 'dictionary.members';
select *
from dictionary.members
where libname = 'WORK' and memtype = 'VIEW';
/* What HELP views are available for dictionary tables? */
title2 'Views for DICTIONARY tables are available to the User';
title3 'dictionary.views';
select *
from dictionary.views
where libname = 'SASHELP' and memname not like 'M%';
title2 'Description of SASHELP views';
describe view sashelp.vcatalg
sashelp.vcolumn
sashelp.vextfl
sashelp.vindex
sashelp.vmember
sashelp.voption
sashelp.vsacces
sashelp.vscatlg
sashelp.vslib
sashelp.vstable
sashelp.vstabvw
sashelp.vsview
sashelp.vtable
sashelp.vview;
quit;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
Where does my new table, PAPER, reside?
dictionary.members
Library Member Engine
Name Member Name Type DBMS Member Type Name Indexes
Path Name
-------------------------------------------------------------------------------------------------------------
WORK PAPER DATA V9 yes
C:\Samples\_TD224
When was the table created?
dictionary.tables
Library
Name Member Name Date Created Date Modified
-----------------------------------------------------------------------
WORK PAPER 17JUN05:09:59:47 17JUN05:09:59:48
Tell me more about the PAPER table.
dictionary.tables
Number of Type of
Number of Physical Observation Password Compression
Member Name Variables Observations Length Protection Encryption Routine
----------------------------------------------------------------------------------------------------
PAPER 5 13 64 --- NO NO
How many observations have been deleted
dictionary.tables
Number of Number of
Physical Deleted
Member Name Observations Observations
-----------------------------------------------------
PAPER 13 1
What Catalog information is available?
dictionary.catalogs
Member Object
Member Name Type Object Name Type Object Description Object Alias
------------------------------------------------------------------------------------------------------------------------------
BASE CATALOG HELP HELP No More Help
When was the Catalog created or last modified?
dictionary.catalogs
Library Object
Name Member Name Object Name Type Date Modified Date Created
------------------------------------------------------------------------------------------------------------
SASHELP BASE HELP HELP 01MAY04:00:00:00 24OCT96:15:45:16
Do the columns have formats, informats, or labels?
dictionary.columns
Column Name Column Label Column Format Column Informat
----------------------------------------------------------------------------------------------------------
author
section
title Paper Title
time TIME5.
duration
Show me column position in PAPER table
dictionary.columns
Column
Column Number
Column Name Position in Table
---------------------------------------------
author 16 1
section 24 2
title 32 3
time 0 4
duration 8 5
Index information for PAPER table
dictionary.indexes
Position of
Column Column in
Index Concatenated Unique
Column Name Type Index Name Key Option
-------------------------------------------------------------------------------------
author COMPOSITE ast 0 yes
section COMPOSITE ast 8 yes
title COMPOSITE ast 16 yes
author SIMPLE author .
What are my current page and line size options set to?
dictionary.options
Option
Option Name Option Setting Option Description Location
-----------------------------------------------------------------------------------------
LINESIZE 132 Line size for SAS log and Portable
SAS procedure output
PAGESIZE 60 Number of lines printed Portable
per page of output
Tell me where my VIEWS reside?
dictionary.views
Library Member Engine
Name Member Name Type Name
-------------------------------------------------------
WORK HALFHR VIEW SASESQL
WORK PM VIEW SASESQL
View information is stored in dictionary.members too!
dictionary.members
Library Member Engine
Name Member Name Type DBMS Member Type Name Indexes
Path Name
-------------------------------------------------------------------------------------------------------------
WORK HALFHR VIEW V9 no
C:\Samples\_TD224
WORK PM VIEW V9 no
C:\Samples\_TD224
Views for DICTIONARY tables are available to the User
dictionary.views
Library Member Engine
Name Member Name Type Name
-------------------------------------------------------
SASHELP VALLOPT VIEW SASESQL
SASHELP VCATALG VIEW SASESQL
SASHELP VCFORMAT VIEW SASESQL
SASHELP VCHKCON VIEW SASESQL
SASHELP VCNCOLU VIEW SASESQL
SASHELP VCNTABU VIEW SASESQL
SASHELP VCOLUMN VIEW SASESQL
SASHELP VDCTNRY VIEW SASESQL
SASHELP VENGINE VIEW SASESQL
SASHELP VEXTFL VIEW SASESQL
SASHELP VFORMAT VIEW SASESQL
SASHELP VGOPT VIEW SASESQL
SASHELP VINDEX VIEW SASESQL
SASHELP VLIBNAM VIEW SASESQL
SASHELP VMACRO VIEW SASESQL
SASHELP VMEMBER VIEW SASESQL
SASHELP VOPTION VIEW SASESQL
SASHELP VREFCON VIEW SASESQL
SASHELP VREMEMB VIEW SASESQL
SASHELP VSACCES VIEW SASESQL
SASHELP VSCATLG VIEW SASESQL
SASHELP VSLIB VIEW SASESQL
SASHELP VSTABLE VIEW SASESQL
SASHELP VSTABVW VIEW SASESQL
SASHELP VSTYLE VIEW SASESQL
SASHELP VSVIEW VIEW SASESQL
SASHELP VTABCON VIEW SASESQL
SASHELP VTABLE VIEW SASESQL
SASHELP VTITLE VIEW SASESQL
SASHELP VVIEW VIEW SASESQL
This example demonstrates basic SQL DICTIONARY statements.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL |
| Date Modified: | 2005-08-24 16:06:32 |
| Date Created: | 2005-05-23 13:53:41 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |