DATA Step, Macro, Functions and more

Macro program to pull out library details from the server

Reply
New Contributor
Posts: 2

Macro program to pull out library details from the server

I am looking to develop a macro so that that macro can pull out the list of libraries details from a server along with the memtype information.

For example I am working on SASApp Server and the server has 50 libraries, in these libraries each library contains 50 to 60 tables so I need to pull out all the information about the libraries and tables along with the libref, path and schema details.

Eventually we can use Proc contents data=libref._all_ out=dataset name; run;

The problem here is every time I have to give the libref name to pull out the information about a paticular library. I need the program should itself assign the libref and populate information about the list of libraries and memtype details.

Contributor
Posts: 53

Re: Macro program to pull out library details from the server

Is this UNIX or Windows or anything else? It looks like you will need to either embed your SAS code within the native environment's programming language or the other way around. In UNIX, this can be achieved using shell scripting and some SAS; for other environments, we will have to check. Either way, some more details might help.

Super User
Posts: 5,497

Re: Macro program to pull out library details from the server

Are you able to get an automated list of the folders you would like to report on?  If so, the rest is nearly easy.  If not, how would SAS know which folders to examine?  You wouldn't want to examine every folder on the system.  You wouldn't even want to use the metadata from SASHELP unless you are willing to hardcode the exceptions like WORK, MAPS, etc.

Super User
Posts: 11,343

Re: Macro program to pull out library details from the server

If you see the information you need then you could use the Dictionary tables with proc sql.

From the online help:


DICTIONARY Tables and Associated SASHELP Views


DICTIONARY
Table

SASHELP View

Description

CATALOGS

VCATALG

Contains
information about known SAS catalogs.

CHECK_CONSTRAINTS

VCHKCON

Contains
information about known check constraints.

COLUMNS

VCOLUMN

Contains
information about columns in all known tables.

CONSTRAINT_COLUMN_USAGE

VCNCOLU

Contains
information about columns that are referred to by integrity constraints.

CONSTRAINT_TABLE_USAGE

VCNTABU

Contains information about tables that have integrity constraints defined on them.

DATAITEMS

VDATAIT

Contains
information about known information map data items.

DESTINATIONS

VDEST

Contains
information about known ODS destinations.

DICTIONARIES

VDCTNRY

Contains
information about all DICTIONARY tables.

ENGINES

VENGINE

Contains
information about SAS engines.

EXTFILES

VEXTFL

Contains
information about known external files.

FILTERS

VFILTER

Contains
information about known information map filters.

FORMATS

VFORMAT

VCFORMAT

Contains
information about currently accessible formats and informats.

FUNCTIONS

VFUNC

Contains
information about currently accessible functions.

GOPTIONS

VGOPT

VALLOPT

Contains
information about currently defined graphics options (SAS/GRAPH
software). SASHELP.VALLOPT includes SAS system options as well as graphics
options.

INDEXES

VINDEX

Contains
information about known indexes.

INFOMAPS

VINFOMP

Contains
information about known information maps.

LIBNAMES

VLIBNAM

Contains
information about currently defined SAS libraries.

MACROS

VMACRO

Contains
information about currently defined macro variables.

MEMBERS

VMEMBER

VSACCES

VSCATLG

VSLIB

VSTABLE

VSTABVW

VSVIEW

Contains
information about all objects that are in currently defined SAS libraries.
SASHELP.VMEMBER contains information for all member types; the other SASHELP
views are specific to particular member types (such as tables or views).

OPTIONS

VOPTION

VALLOPT

Contains
information about SAS system options. SASHELP.VALLOPT includes graphics options
as well as SAS system options.

REFERENTIAL_CONSTRAINTS

VREFCON

Contains
information about referential constraints.

REMEMBER

VREMEMB

Contains
information about known remembers.

STYLES

VSTYLE

Contains
information about known ODS styles.

TABLE_CONSTRAINTS

VTABCON

Contains
information about integrity constraints in all known tables.

TABLES

VTABLE

Contains
information about known tables.

TITLES

VTITLE

Contains
information about currently defined titles and footnotes.

VIEWS

VVIEW

Contains
information about known data views.

VIEW_SOURCES

Not available

Contains a
list of tables (or other views) referenced by the SQL or DATASTEP view, and a
count of the number of references.


Retrieving Information about DICTIONARY Tables and SASHELP Views



To see how each
DICTIONARY table is defined, submit a DESCRIBE TABLE statement. This example
shows the definition of DICTIONARY.TABLES:

proc sql; describe table dictionary.tables;

The results are
written to the SAS log.


Definition of DICTIONARY.TABLES
 NOTE: SQL table DICTIONARY.TABLES was created like:  create table DICTIONARY.TABLES ( 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', memlabel char(256) label='Data Set Label', typemem char(8) label='Data Set Type', crdate num format=DATETIME informat=DATETIME label='Date Created', modate num format=DATETIME informat=DATETIME label='Date Modified', nobs num label='Number of Physical Observations', obslen num label='Observation Length', nvar num label='Number of Variables', protect char(3) label='Type of Password Protection', compress char(8) label='Compression Routine', encrypt char(8) label='Encryption', npage num label='Number of Pages', filesize num label='Size of File', pcompress num label='Percent Compression', reuse char(3) label='Reuse Space', bufsize num label='Bufsize', delobs num label='Number of Deleted Observations', nlobs num label='Number of Logical Observations', maxvar num label='Longest variable name', maxlabel num label='Longest label', maxgen num label='Maximum number of generations', gen num label='Generation number', attr char(3) label='Data Set Attributes', indxtype char(9) label='Type of Indexes', datarep char(32) label='Data Representation', sortname char(8) label='Name of Collating Sequence', sorttype char(4) label='Sorting Type', sortchar char(8) label='Charset Sorted By', reqvector char(24) format=$HEX48 informat=$HEX48 label='Requirements Vector', datarepname char(170) label='Data Representation Name', encoding char(256) label='Data Encoding', audit char(8) label='Audit Trail Active?', audit_before char(8) label='Audit Before Image?', audit_admin char(8) label='Audit Admin Image?', audit_error char(8) label='Audit Error Image?', audit_data char(8) label='Audit Data Image?', num_character num label='Number of Character Variables', num_numeric num label='Number of Numeric Variables' );

Similarly, you can
use the DESCRIBE VIEW statement in PROC SQL to determine how a SASHELP view is
defined. Here is an example:
proc sql; describe view sashelp.vtable;
Ask a Question
Discussion stats
  • 3 replies
  • 234 views
  • 0 likes
  • 4 in conversation