Date: Wed, 22 Dec 1999 10:38:47 -0700
Reply-To: Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject: (MVS): Re[2]: (MVS): Re:Dictionary Tables
Content-Type: text/plain; charset=us-ascii
You might also want to look at my paper, "Some Utility Applications Of The Dictionary Tables in PROC SQL", given at WUSS '98. None of its examples are as complicated as what you're doing below, but it has the advantage of being available online at <http://www.qsl.net/kd6ttl/sas/sqlutilov.pdf>.
--
JackHamilton@FirstHealth.com
Development Manager, Technical Group
METRICS Department, First Health
West Sacramento, California USA
>>> "RAITHEM" <RAITHEM@WESTAT.COM> 12/22/1999 7:16 am >>>
The highly prolific, seemingly indefatigable Peter Crawford posted a very
interesting program and SAS logs that highlight the DICTIONARY tables.
<<Peter's entire posting can be found beneath the Sig line, below>>
Peter, it is highly ironic that at the time that I read your posting, I was
developing yet another application wherein I exploited the Dictionary tables.
For SAS-L-ers who may be thinking: "very interesting stuff, these DICTIONARY
tables and their metadata, but how would I ever use them?", I've included part
of my in-development program, below.
(When I first saw Frank DiIorio and Nancy Michal's SESUG'94 presentations on
Dictionary Tables and SASHELP views, I was hot-to-trot to exploit them--the
Tables and Views, not Frank and Nancy! But, for a couple of months, I could not
find a suitable use. And then, one day...
The SESUG reference is:
Proceedings of the Second Annual SouthEast SAS Users
Group Conference
SESUG'94
The papers are:
Data About Data: An Introduction to Dictionary Views and
Tables
Frank C. DiIorio
pages 71 - 73
Dictionary Tables and SASHELP Views: An Introductory
Tutorial
Nancy J. Michal
pages 327 - 330)
Here is part of a program wherein I exploit the DICTIONARY Tables. In the
larger program, I extract data from SYBASE via SAS/ACCESS to SYBASE, crunch it,
and download it to an NT Server via SAS/CONNECT. On the NT server I run PROC
SHEWHART to produce SHEWHART charts of various analysis, and then HTML-ize the
output (SAS 6.16, SAS/IntrNet) and send it back to the client's web viewer.
(The "send it back to the client's web viewer" is the "in development" part of
this program:-( ). My problems were the following:
1. Because of the variable nature of the data, the user could generate 1 to 99
SHEWHART charts. I could not know the exact number ahead of time, so I needed a
generalized way to determine the number of SHEWHART charts in the catalog.
2. PROC SHEWHART puts the charts into the catalog in LIFO (Last In First Out)
order. That is, if I produce three charts, they end up in the catalog as:
SHEWHAR2
SHEWHAR1
SHEWHART
I wanted to have them replayed in the order that they were created: SHEWHART,
SHEWHAR1, SHEWHAR2, etc.
So, I exploited the Dictionary Tables:
OPTIONS MACROGEN SYMBOLGEN MLOGIC MPRINT;
/**************************************************************/
/* This macro reads the WORK.GSEG catalog, captures the number*/
/* of objects (graphs) and their names, and PROC GREPLAYS them*/
/* in their original order. */
/**************************************************************/
%MACRO ANYOBJCT;
**************************************************************;
* Capture the objects in the WORK.GSEG catalog. *;
**************************************************************;
proc sql noprint;
create table grfcount as
select libname, memname, objname, objtype
from dictionary.catalogs
where libname = "WORK" and
memname = "GSEG"
;
quit;
****************************************************************;
* Set up macro variables. TOTOBJS will have total number of *;
* objects in WORK.GSEG. OBJCT01 - OBJCTnn will have the name *;
* of each object in the catalog. *;
****************************************************************;
data _null_;
set grfcount nobs=totobjs;
if _n_ = 1 then call symput('totobjs',totobjs);
call symput('objct' || trim(left(put(totobjs,z2.))),objname);
totobjs = totobjs - 1;
run;
****************************************************************;
* Replay the graph objects in the proper order. *;
****************************************************************;
%do i = 1 %to &totobjs;
proc greplay NOFS;
igout work.gseg;
%if &i < 10 %then replay &&objct0&i;
%else replay &&objct&i;
;
quit;
%END;
/***************************/
/* End of ANYOBJCT Macro. */
/***************************/
%MEND ANYOBJCT;
/***************************/
/* Invoke ANYOBJCT Macro. */
/***************************/
%ANYOBJCT;
In the program, above, the CATALOG DICTIONARY Table is queried to build a table,
GRFCOUNT, which contains the LIBNAME, MEMNAME, and OBJNAME of all observations
in the DICTIONARY tables that match the LIBNAME of "WORK" and the MEMNAME
(catalog name) of "GSEG". Once I have the object names segregated, I create a
series of Macro variables, OBJCT01 - OBJCTnn, which contain the object names.
Note that I number them in reverse order so that I can countermand the LIFO
order of the catalog. Finally, I use PROC GREPLAY in a Macro %DO loop to replay
the objects (graphs) in the required order.
As Peter "said" in his posting, the DICTIONARY Tables are great sources of
information. I hope that your imagination is greater than mine was when I first
found out about them, and that you can "go to town" exploiting them!
I want to wish you, yes Y-O-U, a great Holiday season and a Happy New Year! I
will look forward to reading your questions, comments, solutions, gripes,
observations, off-topic humor, flames, peace-making, SAS-bashing, SAS-praising,
etc. postings next year on this most fascinating communication medium that we
call: SAS-L!
I hope that this explanation proves helpful now, and in the future!
Of course, all of these opinions and insights are my own, and do not
reflect those of my organization or my associates.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel, Westat
E-mail: raithem@westat.com
Author: Tuning SAS Applications in the MVS Environment
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If a little knowledge is dangerous--where is the man who has so much as to be
out of danger? -- Thomas Huxley
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
____________________Reply Separator____________________
Subject: Re: (MVS): Re:Dictionary Tables
Author: Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Date: 12/21/99 1:10 PM
Just before the rush home for the holidays, I thought I'd polish up on
these DICTIONARY tables. Mike Raithel and Jack Hamilton have provided
keys to unlock these great sources of info. I can only add little.
I like to avoid too much of cutting from the log and pasting into the
editor followed by more cutting. And this time, it's only to create
describe statements for any dictionary tables that may be present.
The elements of the 34 line program below provide these features
1 dictionary tables exposed and described
2 proc sql generating code in macro vars with INTO : host vars
3 reading the current session log in a data step
4 using input @'string' statements to select parts of the log
5 capturing, overwriting and reinstating current option values
6 + a question to follow up....
the question is,
How can we document any indexes which exist for dictionary tables ?
>>>>The program
/* from ideas prompted by Jack Hamilton on SAS-L
"describe" any dictionary tables
*/
*** first.. a little tidy up for a neat log ;
%let oldops = %getop(ls) %getop(center);
option ls=70 nocenter ;
proc printto log = "dtabls.&sysver..log" new; /* capture log */
run;
proc sql noprint; /* generate list of dictionary tables */
select 'describe view sashelp.' || memname
into :SHOVIEWS separated by '; '
from dictionary.views
where libname = 'SASHELP'
;
&SHOVIEWS ;
quit;
proc printto; /* release the log with the list */
run;
DATA B; /* read the dictionary table names */
INFILE "dtabls.&sysver..log" dlm =';' ;
INPUT @' is defined as:'
@'select '
@'from DICTIONARY.' tabl $ ;
run;
proc sql noprint; /* generate describe statements */
select distinct
'describe table dictionary.' || tabl
into :descr separated by ' ; '
from _last_
;
%put %quote(&descr ); /* some documentation before the run */
&descr;
quit;
** reinstate; options &oldops;
/*****************************
macro %getop was defined as
%macro getop(o);
%sysfunc( getoption( &o, keyword ) )
%mend getop; ********/
>>>>testing proof (win95 v6.12 ts045 )
[ Section: 1/1 File: gendesc.log UUencoded by: Turnpike Version 4.00 ]
Embedded uuencode file has been extracted
sum -r/size 44797/7998 section (from "begin" to "end")
sum -r/size 1382/5782 entire input file
>>>>v8 testing
[ Section: 1/1 File: gendesc.log UUencoded by: Turnpike Version 4.00 ]
Embedded uuencode file has been extracted
sum -r/size 44206/9532 section (from "begin" to "end")
sum -r/size 9961/6895 entire input file
RAITHEM <RAITHEM@WESTAT.COM> Writes
>In a thread that is now colder than the remains of the Endurance (--a stretch,
Compliments of the season,
and HTH
--
Peter Crawford (_knowledge_ is a poor substitute for *real* experience,
but they make a great team)
